Class: Krkt::Bigquery

Inherits:
Object
  • Object
show all
Defined in:
lib/krkt/bigquery.rb

Overview

classe para processar bigquery & kraken

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(pop) ⇒ Bigquery

Returns API bigquery & kraken.

Parameters:

  • pop (Thor::CoreExt::HashWithIndifferentAccess) —

    opcoes trabalho

Options Hash (pop):

  • :h (Hash) — default: {} —

    configuracao ajuste reposicionamento temporal

  • :v (Boolean) — default: false —

    mostra transacoes trades & ledger?

  • :t (Boolean) — default: false —

    mostra transacoes todas ou somente novas?



26
27
28
29
30
31
 
# File 'lib/krkt/bigquery.rb', line 26

def initialize(pop)
  # usa env GOOGLE_APPLICATION_CREDENTIALS para obter credentials
  # @see https://cloud.google.com/bigquery/docs/authentication/getting-started
  @api = Google::Cloud::Bigquery.new
  @ops = pop
end

Instance Attribute Details

#api ⇒ Google::Cloud::Bigquery (readonly)

Returns API bigquery.

Returns:

  • (Google::Cloud::Bigquery) —

    API bigquery



13
14
15
 
# File 'lib/krkt/bigquery.rb', line 13

def api
  @api
end

#job ⇒ Google::Cloud::Bigquery::QueryJob (readonly)

Returns job bigquery.

Returns:

  • (Google::Cloud::Bigquery::QueryJob) —

    job bigquery



15
16
17
 
# File 'lib/krkt/bigquery.rb', line 15

def job
  @job
end

#ops ⇒ Thor::CoreExt::HashWithIndifferentAccess (readonly)

Returns opcoes trabalho.

Returns:

  • (Thor::CoreExt::HashWithIndifferentAccess) —

    opcoes trabalho



17
18
19
 
# File 'lib/krkt/bigquery.rb', line 17

def ops
  @ops
end

#sqr ⇒ Google::Cloud::Bigquery::Data (readonly)

Returns resultado do SQL.

Returns:

  • (Google::Cloud::Bigquery::Data) —

    resultado do SQL



19
20
21
 
# File 'lib/krkt/bigquery.rb', line 19

def sqr
  @sqr
end

Instance Method Details

#dml(cmd) ⇒ Integer

cria Data Manipulation Language (DML) job bigquery

Parameters:

  • cmd (String) —

    comando SQL a executar

Returns:

  • (Integer) —

    numero linhas afetadas



122
123
124
 
# File 'lib/krkt/bigquery.rb', line 122

def dml(cmd)
  job?(cmd) ? 0 : job.num_dml_affected_rows
end

#job?(cmd) ⇒ Boolean

cria job bigquery & verifica execucao

Parameters:

  • cmd (String) —

    comando SQL a executar

Returns:

  • (Boolean) —

    job ok?



102
103
104
105
106
107
 
# File 'lib/krkt/bigquery.rb', line 102

def job?(cmd)
  @job = api.query_job(cmd)
  @job.wait_until_done!
  puts(@job.error['message']) if @job.failed?
  @job.failed?
end

#processa ⇒ Object

insere transacoes novas nas tabelas ust (trades), usl (ledger)



46
47
48
49
 
# File 'lib/krkt/bigquery.rb', line 46

def processa
  puts(format("%<n>2i TRADES INSERIDAS #{BD}.ust", n: transacoes.trades.count.positive? ? dml(ust_ins) : 0))
  puts(format("%<n>2i LEDGER INSERIDAS #{BD}.usl", n: transacoes.ledger.count.positive? ? dml(usl_ins) : 0))
end

#sql(cmd, red = []) ⇒ Google::Cloud::Bigquery::Data

cria Structured Query Language (SQL) job bigquery

Parameters:

  • cmd (String) —

    comando SQL a executar

  • red (String) (defaults to: []) —

    resultado quando SQL tem erro

Returns:

  • (Google::Cloud::Bigquery::Data) —

    resultado do SQL



114
115
116
 
# File 'lib/krkt/bigquery.rb', line 114

def sql(cmd, red = [])
  @sqr = job?(cmd) ? red : job.data
end

#transacoes ⇒ Kraken

Returns API kraken - obter saldos & transacoes trades e ledger.

Returns:

  • (Kraken) —

    API kraken - obter saldos & transacoes trades e ledger



34
35
36
37
38
39
40
41
42
43
 
# File 'lib/krkt/bigquery.rb', line 34

def transacoes
  @transacoes ||= Kraken.new(
    {
      sl: sql("select * from #{BD}.ussl")[0],
      nt: sql("select * from #{BD}.ustx order by time,txid"),
      nl: sql("select * from #{BD}.uslx order by time,txid")
    },
    ops
  )
end

#usl_ins ⇒ String

Returns comando insert SQL formatado usl (ledger).

Returns:

  • (String) —

    comando insert SQL formatado usl (ledger)



81
82
83
84
 
# File 'lib/krkt/bigquery.rb', line 81

def usl_ins
  "insert #{BD}.usl(txid,refid,time,type,aclass,asset,amount,fee" \
  ") VALUES#{transacoes.ledger.map { |k, v| usl_val(k, v) }.join(',')}"
end

#usl_val(idx, hes) ⇒ String

Returns valores formatados usl (ledger).

Returns:

  • (String) —

    valores formatados usl (ledger)



87
88
89
90
91
92
93
94
95
96
 
# File 'lib/krkt/bigquery.rb', line 87

def usl_val(idx, hes)
  "('#{idx}'," \
  "'#{hes['refid']}'," \
  "PARSE_DATETIME('%s', '#{String(hes['time'].round)}')," \
  "'#{hes['type']}'," \
  "#{hes['aclass'].length.zero? ? 'null' : "'#{hes['aclass']}'"}," \
  "'#{hes['asset']}'," \
  "cast(#{hes['amount']} as numeric)," \
  "cast(#{hes['fee']} as numeric))"
end

#ust_ins ⇒ String

Returns comando insert SQL formatado ust (trades).

Returns:

  • (String) —

    comando insert SQL formatado ust (trades)



52
53
54
55
 
# File 'lib/krkt/bigquery.rb', line 52

def ust_ins
  "insert #{BD}.ust(txid,ordertxid,pair,time,type,ordertype,price,cost,fee,vol,margin,misc,ledgers,dias" \
  ") VALUES#{transacoes.trades.map { |k, v| ust_val1(k, v) }.join(',')}"
end

#ust_val1(idx, hes) ⇒ String

Returns valores formatados ust (trades parte1).

Returns:

  • (String) —

    valores formatados ust (trades parte1)



58
59
60
61
62
63
64
65
66
67
68
69
 
# File 'lib/krkt/bigquery.rb', line 58

def ust_val1(idx, hes)
  "('#{idx}'," \
  "'#{hes['ordertxid']}'," \
  "'#{hes['pair']}'," \
  "PARSE_DATETIME('%s', '#{String(hes['time'].round)}')," \
  "'#{hes['type']}'," \
  "'#{hes['ordertype']}'," \
  "cast(#{hes['price']} as numeric)," \
  "cast(#{hes['cost']} as numeric)," \
  "cast(#{hes['fee']} as numeric)," \
  "#{ust_val2(idx, hes)}"
end

#ust_val2(idx, hes) ⇒ String

Returns valores formatados ust (trades parte2).

Returns:

  • (String) —

    valores formatados ust (trades parte2)



72
73
74
75
76
77
78
 
# File 'lib/krkt/bigquery.rb', line 72

def ust_val2(idx, hes)
  "cast(#{hes['vol']} as numeric)," \
  "cast(#{hes['margin']} as numeric)," \
  "#{hes['misc'].length.zero? ? 'null' : "'#{hes['misc']}'"}," \
  "'#{transacoes.ledger.select { |_, v| v['refid'] == idx }.keys.join(',') || ''}'," \
  "#{Integer(ops[:h][idx] || 0)})"
end