class Eost::Bigquery

(see Bigquery)

trabalhar com folhas calculo bloks.io & dados no bigquery

Attributes

api[R]

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

folha[R]

@return [Roo::CSV] folha calculo a processar

job[R]

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

linha[R]

@return [Hash<Symbol, Boolean>] opcoes trabalho com linhas

row[R]

@return [Array] row folha calculo em processamento

sqr[R]

@return (see sql)

Public Class Methods

new(csv = '', ops = { e: false, m: false, i: false }) click to toggle source

@param [String] csv folha calculo para processar @param [Hash<Symbol, Boolean>] ops opcoes trabalho com linhas @option ops [Boolean] :e (false) apaga linha igual? @option ops [Boolean] :m (false) apaga linhas existencia multipla? @option ops [Boolean] :i (false) insere linha nova? @return [Bigquery] acesso folhas calculo bloks.io & correspondente bigquery dataset

# File lib/eost/bigquery.rb, line 33
def initialize(csv = '', ops = { e: false, m: false, i: false })
  @folha = Roo::CSV.new(csv) if csv.size.positive?
  @linha = ops

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

Public Instance Methods

act(htx) click to toggle source

@param [Hash] htx transacao normal @return [Hash] dados da acao

# File lib/eost/bigquery.rb, line 128
def act(htx)
  htx['action_trace']['act']
end
act_data(htx) click to toggle source

@param [Hash] htx transacao normal @return [Hash] dados da acao

# File lib/eost/bigquery.rb, line 134
def act_data(htx)
  act(htx)['data']
end
carteiras() click to toggle source

@return [Carteiras] API eosscan - processar carteiras & transacoes

# File lib/eost/bigquery.rb, line 54
def carteiras
  transacoes
end
dml(cmd) click to toggle source

cria Data Manipulation Language (DML) job bigquery

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

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

@param [Hash] htx transacao ligadas a uma carteira - sem elementos irrelevantes @return [String] valores formatados para insert eos (parte1)

# File lib/eost/bigquery.rb, line 106
def eos_api_val1(htx)
  "(#{Integer(htx['block_num'])}," \
  "DATETIME(TIMESTAMP('#{htx['block_time']}'))," \
  "'#{act(htx)['account']}'," \
  "'#{act(htx)['name']}'," \
  "'#{act_data(htx)['from']}'," \
  "'#{act_data(htx)['to']}'," \
  "#{eos_api_val2(htx)}"
end
eos_api_val2(htx) click to toggle source

@param [Hash] htx transacao ligadas a uma carteira - sem elementos irrelevantes @return [String] valores formatados para insert eos (parte2)

# File lib/eost/bigquery.rb, line 118
def eos_api_val2(htx)
  "#{act_data(htx)['quantity'].to_d}," \
  "'#{act_data(htx)['quantity'][/[[:upper:]]+/]}'," \
  "'#{act_data(htx)['memo']}'," \
  "'#{act_data(htx)}'," \
  "#{Integer(linha[:h][String(htx['block_num'])] || 0)})"
end
eos_csv_val1() click to toggle source

@return [String] valores formatados para insert eos (parte1)

# File lib/eost/bigquery.rb, line 81
def eos_csv_val1
  "#{row[0]}," \
  "'#{Time.parse(row[1]).strftime(DI)}'," \
  "'#{row[2]}'," \
  "#{eos_csv_val2}"
end
eos_csv_val2() click to toggle source

@return [String] valores formatados para insert eos (parte2)

# File lib/eost/bigquery.rb, line 89
def eos_csv_val2
  "'#{row[3]}'," \
  "'#{row[4]}'," \
  "'#{row[5]}'," \
  "#{Float(row[6])}," \
  "'#{row[7]}'," \
  "'#{row[8]}'," \
  "'#{row[9]}',0"
end
eos_fields() click to toggle source

@return [String] campos da tabela eos no bigquery

# File lib/eost/bigquery.rb, line 64
def eos_fields
  'blocknumber,time,contract,action,acfrom,acto,amount,symbol,memo,data,dias'
end
eos_insert_api() click to toggle source

@return [Integer] numero linhas inseridas

# File lib/eost/bigquery.rb, line 100
def eos_insert_api
  dml("INSERT #{BD}.eos(#{eos_fields}) VALUES#{transacoes.novas.map { |e| eos_api_val1(e) }.join(',')}")
end
eos_insert_csv() click to toggle source

@return [Integer] numero linhas inseridas

# File lib/eost/bigquery.rb, line 74
def eos_insert_csv
  return 1 unless linha[:i]

  dml("INSERT #{BD}.eos(#{eos_fields}) VALUES(#{eos_csv_val1})")
end
job?(cmd) click to toggle source

cria job bigquery & verifica execucao

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

# File lib/eost/bigquery.rb, line 142
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 na tabela eos

# File lib/eost/bigquery.rb, line 59
def processa
  puts(format("%<n>2i LINHAS INSERIDAS #{BD}.eos", n: transacoes.novas.count.positive? ? eos_insert_api : 0))
end
processa_csv() click to toggle source

processa folha calculo

# File lib/eost/folhacalculo.rb, line 11
def processa_csv
  n = 0
  folha.sheet(0).parse(header_search: HT) do |r|
    n += 1
    puts n == 1 ? "\n#{folha.info}" : processa_row(r)
  end
end
processa_row(has) click to toggle source

processa linha folha calculo para arquivo

@param [Hash] has da linha em processamento @return [String] texto informativo do processamento

# File lib/eost/folhacalculo.rb, line 23
def processa_row(has)
  @row = has.values

  # array.count  = 0 ==> pode carregar esta linha
  # array.count >= 1 ==> nao carregar esta linha
  sql("select #{eos_fields} #{sql_where}", [{}, {}])

  if row_naoexiste? then row_str + (eos_insert_csv == 1 ? ' NOVA' : ' ERRO')
  elsif row_existe? then row_existente
  else                   row_multiplas
  end
end
row_existe?() click to toggle source

@return [Boolean] linha folha calculo existe no bigquery?

# File lib/eost/folhacalculo.rb, line 75
def row_existe?
  sqr.count == 1
end
row_existente() click to toggle source

@return [String] linha folha calculo existente

# File lib/eost/folhacalculo.rb, line 52
def row_existente
  d = linha[:e] ? dml("delete #{sql_where}") : 0
  "#{row_str} EXISTENTE#{str_apagadas(d)}"
end
row_multiplas() click to toggle source

@return [String] linha folha calculo existencia multipla

# File lib/eost/folhacalculo.rb, line 58
def row_multiplas
  d = linha[:m] ? dml("delete #{sql_where}") : 0
  "#{row_str} MULTIPLAS #{sql.count}#{str_apagadas(d)}"
end
row_naoexiste?() click to toggle source

@return [Boolean] linha folha calculo nao existe no bigquery?

# File lib/eost/folhacalculo.rb, line 70
def row_naoexiste?
  sqr.count.zero?
end
row_r1() click to toggle source

@return [String] linha folha calculo formatada

# File lib/eost/folhacalculo.rb, line 42
def row_r1
  format(R1, v5: row[4], v6: row[5])
end
row_r2() click to toggle source

@return [String] linha folha calculo formatada

# File lib/eost/folhacalculo.rb, line 47
def row_r2
  format(R2, v7: Float(row[6]), v8: row[7])
end
row_str() click to toggle source

@return [String] linha folha calculo formatada

# File lib/eost/folhacalculo.rb, line 37
def row_str
  "#{row[0]} #{Time.parse(row[1]).strftime(DF)} " + row_r1 + row_r2
end
sql(cmd, red = []) click to toggle source

cria Structured Query Language (SQL) job bigquery

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

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

@return [String] parte sql para processamento linhas existentes

# File lib/eost/bigquery.rb, line 69
def sql_where
  "from #{BD}.eos where blocknumber=#{row[0]}"
end
str_apagadas(num) click to toggle source

@param [Integer] num numero linhas apagadas @return [String] texto formatado linhas apagadas

# File lib/eost/folhacalculo.rb, line 65
def str_apagadas(num)
  num.positive? ? " & #{num} APAGADA(S) " : ' '
end
transacoes() click to toggle source

@return [Carteiras] API eosscan - processar transacoes

# File lib/eost/bigquery.rb, line 43
def transacoes
  @transacoes ||= Carteiras.new(
    {
      wb: sql("select * from #{BD}.walletEos order by 1").map { |e| { ax: e[:weos], sl: e[:eos].to_d } },
      nt: sql("select blocknumber,iax from #{BD}.eostx order by 1")
    },
    linha
  )
end