Class: Arquivo::C118bigquery

Inherits:
C118sheets show all
Defined in:
lib/arquivo/bigquery.rb

Overview

permite arquivar dados c118 no bigquery

Instance Attribute Summary collapse

Attributes inherited from C118sheets

#folhas

Instance Method Summary collapse

Methods inherited from C118sheets

#new_credentials, #sheets_credentials

Constructor Details

#initialize ⇒ C118bigquery

Returns acesso bigquery c118.



12
13
14
15
16
17
18
19
 
# 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

Instance Attribute Details

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

Returns API bigquery c118.

Returns:

  • (Google::Cloud::Bigquery) —

    API bigquery c118



9
10
11
 
# File 'lib/arquivo/bigquery.rb', line 9

def big
  @big
end

Instance Method Details

#col_bal ⇒ String

Returns colunas da tabela bal no bigquery.

Returns:

  • (String) —

    colunas da tabela bal no bigquery



41
42
43
 
# 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 ⇒ String

Returns colunas da tabela hise no bigquery.

Returns:

  • (String) —

    colunas da tabela hise no bigquery



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

def col_hise
  'ano,dr,tag,descricao,valor'
end

#dml(sql) ⇒ Integer

executa comando DML (Data Manipulation Language) no bigquery

Returns:

  • (Integer) —

    numero linhas afetadas pelo DML



53
54
55
56
57
58
 
# 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 ⇒ Object

obtem dados da folha c118 & processa no bigquery



22
23
24
25
26
27
28
 
# 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) ⇒ Array<Integer>

executa comandos DML para processa no bigquery

Returns:

  • (Array<Integer>) —

    numero linhas afetadas pelos DMLs



33
34
35
36
37
38
 
# 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