class Krkt::Bigquery

classe para processar bigquery & kraken

Attributes

api[R]

@return [Google::Cloud::Bigquery] API bigquery

job[R]

@return [Google::Cloud::Bigquery::QueryJob] job bigquery

ops[R]

@return [Thor::CoreExt::HashWithIndifferentAccess] opcoes trabalho

sqr[R]

@return (see sql)

Public Class Methods

new(pop) click to toggle source

@param [Thor::CoreExt::HashWithIndifferentAccess] pop opcoes trabalho @option pop [Hash] :h ({}) configuracao ajuste reposicionamento temporal @option pop [Boolean] :v (false) mostra transacoes trades & ledger? @option pop [Boolean] :t (false) mostra transacoes todas ou somente novas? @return [Bigquery] API bigquery & kraken

# 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

Public Instance Methods

dml(cmd) click to toggle source

cria Data Manipulation Language (DML) job bigquery

@param cmd (see sql) @return [Integer] numero linhas afetadas

# File lib/krkt/bigquery.rb, line 122
def dml(cmd)
  job?(cmd) ? 0 : job.num_dml_affected_rows
end
job?(cmd) click to toggle source

cria job bigquery & verifica execucao

@param cmd (see sql) @return [Boolean] job ok?

# 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() click to toggle source

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

# 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 = []) click to toggle source

cria Structured Query Language (SQL) job bigquery

@param [String] cmd comando SQL a executar @param [String] red resultado quando SQL tem erro @return [Google::Cloud::Bigquery::Data] resultado do SQL

# File lib/krkt/bigquery.rb, line 114
def sql(cmd, red = [])
  @sqr = job?(cmd) ? red : job.data
end
transacoes() click to toggle source

@return [Kraken] API kraken - obter saldos & transacoes trades e ledger

# 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() click to toggle source

@return [String] comando insert SQL formatado usl (ledger)

# 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) click to toggle source

@return [String] valores formatados usl (ledger)

# 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() click to toggle source

@return [String] comando insert SQL formatado ust (trades)

# 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) click to toggle source

@return [String] valores formatados ust (trades parte1)

# 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) click to toggle source

@return [String] valores formatados ust (trades parte2)

# 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