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