class Arquivo::C118bigquery

permite arquivar dados c118 no bigquery

Attributes

big[R]

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

Public Class Methods

new() click to toggle source

@return [C118bigquery] acesso bigquery c118

# File lib/arquivo/bigquery.rb, line 12
def initialize
  # inicializar API sheets com ID cliente & credenciais
  sheets_credentials

  # This uses Application Default Credentials to authenticate.
  # @see https://cloud.google.com/bigquery/docs/authentication/getting-started
  @big = Google::Cloud::Bigquery.new
end

Public Instance Methods

col_bal() click to toggle source

@return [String] colunas da tabela bal no bigquery

# File lib/arquivo/bigquery.rb, line 41
def col_bal
  'data,entidade,documento,descricao,valor,tag,dr,banco,conta,ano,id4,dref,daa,paga,desb'
end
col_hise() click to toggle source

@return [String] colunas da tabela hise no bigquery

# File lib/arquivo/bigquery.rb, line 46
def col_hise
  'ano,dr,tag,descricao,valor'
end
dml(sql) click to toggle source

executa comando DML (Data Manipulation Language) no bigquery

@return [Integer] numero linhas afetadas pelo DML

# File lib/arquivo/bigquery.rb, line 53
def dml(sql)
  job = big.query_job(sql)
  job.wait_until_done!
  puts job.error if job.failed?
  job.num_dml_affected_rows
end
processa_big() click to toggle source

obtem dados da folha c118 & processa no bigquery

# File lib/arquivo/bigquery.rb, line 22
def processa_big
  # folha c118-contas
  s = '1PbiMrtTtqGztZMhe3AiJbDS6NQE9o3hXebnQEFdt954'
  a = folhas.get_spreadsheet_values(s, 'cbd!AJ2').values.flatten[0]
  i = folhas.get_spreadsheet_values(s, 'bal!R2:R').values.flatten.join(',')
  puts 'processamento bigquery feito para ano ' + a + ": [del_bal,del_hise,ins_bal,ins_hise] #{sql_big(a, i)}"
end
sql_big(ano, lst) click to toggle source

executa comandos DML para processa no bigquery

@return [Array<Integer>] numero linhas afetadas pelos DMLs

# File lib/arquivo/bigquery.rb, line 33
def sql_big(ano, lst)
  [dml('delete FROM arquivo.bal  WHERE ano=' + ano),
   dml('delete FROM arquivo.hise WHERE ano=' + ano),
   dml("INSERT arquivo.bal (#{col_bal}) VALUES" + lst),
   dml("INSERT arquivo.hise(#{col_hise}) select * from arquivo.vhe where ano=" + ano)]
end