Class: Cex::Bigquery

Inherits:
Object
  • Object
show all
Defined in:
lib/cex/bigquery1.rb,
lib/cex/bigquery2.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(pop) ⇒ Bigquery

Returns API bigquery & kraken/bitcoinde/paymium/therock.

Parameters:

  • pop (Thor::CoreExt::HashWithIndifferentAccess) —

    opcoes trabalho

Options Hash (pop):

  • :h (Hash) — default: {} —

    configuracao ajuste reposicionamento temporal

  • :v (Boolean) — default: false —

    mostra transacoes trades & ledger?

  • :t (Boolean) — default: false —

    mostra transacoes todas ou somente novas?



26
27
28
29
30
31
 
# File 'lib/cex/bigquery1.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

Instance Attribute Details

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

Returns API bigquery.

Returns:

  • (Google::Cloud::Bigquery) —

    API bigquery



13
14
15
 
# File 'lib/cex/bigquery1.rb', line 13

def api
  @api
end

#job ⇒ Google::Cloud::Bigquery::QueryJob (readonly)

Returns job bigquery.

Returns:

  • (Google::Cloud::Bigquery::QueryJob) —

    job bigquery



15
16
17
 
# File 'lib/cex/bigquery1.rb', line 15

def job
  @job
end

#ops ⇒ Thor::CoreExt::HashWithIndifferentAccess (readonly)

Returns opcoes trabalho.

Returns:

  • (Thor::CoreExt::HashWithIndifferentAccess) —

    opcoes trabalho



17
18
19
 
# File 'lib/cex/bigquery1.rb', line 17

def ops
  @ops
end

#sqr ⇒ Google::Cloud::Bigquery::Data (readonly)

Returns resultado do SQL.

Returns:

  • (Google::Cloud::Bigquery::Data) —

    resultado do SQL



19
20
21
 
# File 'lib/cex/bigquery1.rb', line 19

def sqr
  @sqr
end

Instance Method Details

#apide ⇒ Bitcoinde

Returns API Bitcoinde - obter saldos & transacoes trades e ledger.

Returns:

  • (Bitcoinde) —

    API Bitcoinde - obter saldos & transacoes trades e ledger



46
47
48
49
50
51
52
53
54
55
 
# File 'lib/cex/bigquery1.rb', line 46

def apide
  @apide ||= Bitcoinde.new(
    {
      sl: sql("select sum(btc) btc from #{BD}.desl")[0],
      nt: sql("select * from #{BD}.detx order by time,txid"),
      nl: sql("select * from #{BD}.delx order by time,txid")
    },
    ops
  )
end

#apifr ⇒ Paymium

Returns API Paymium - obter saldos & transacoes ledger.

Returns:

  • (Paymium) —

    API Paymium - obter saldos & transacoes ledger



58
59
60
61
62
63
64
65
66
 
# File 'lib/cex/bigquery1.rb', line 58

def apifr
  @apifr ||= Paymium.new(
    {
      sl: sql("select sum(btc) btc,sum(eur) eur from #{BD}.frsl")[0],
      nl: sql("select * from #{BD}.frlx order by time,txid")
    },
    ops
  )
end

#apimt ⇒ TheRock

Returns API TheRock - obter saldos & transacoes ledger.

Returns:

  • (TheRock) —

    API TheRock - obter saldos & transacoes ledger



69
70
71
72
73
74
75
76
77
 
# File 'lib/cex/bigquery1.rb', line 69

def apimt
  @apimt ||= TheRock.new(
    {
      sl: sql("select sum(btc) btc,sum(eur) eur from #{BD}.mtsl")[0],
      nl: sql("select * from #{BD}.mtlx order by time,txid")
    },
    ops
  )
end

#apius ⇒ Kraken

Returns API kraken - obter saldos & transacoes trades e ledger.

Returns:

  • (Kraken) —

    API kraken - obter saldos & transacoes trades e ledger



34
35
36
37
38
39
40
41
42
43
 
# File 'lib/cex/bigquery1.rb', line 34

def apius
  @apius ||= Kraken.new(
    {
      sl: sql("select sum(btc) xxbt,sum(eth) xeth,sum(eos) eos,sum(eur) zeur 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

#del_ins ⇒ String

Returns comando insert SQL formatado del (ledger).

Returns:

  • (String) —

    comando insert SQL formatado del (ledger)



25
26
27
 
# File 'lib/cex/bigquery2.rb', line 25

def del_ins
  "insert #{BD}.del(time,tp,qtxt,id,qt,fee,lgid) VALUES#{apide.ledger.map { |h| del_val(h) }.join(',')}"
end

#del_val(hlx) ⇒ String

Returns valores formatados del (ledger).

Examples:

{
  deposit_id: '177245',
  txid: '84f9e85bc5709cd471e3d58a7d0f42d2c4a7bbd888cabf844e200efbf0a7fda2',
  address: '1KK6HhG3quojFS4CY1mPcbyrjQ8BMDQxmT',
  amount: '0.13283',
  confirmations: 6,
  state: 2,
  created_at: '2014-01-31T22:01:30+01:00'
}
{
  withdrawal_id: '136605',
  address: '1K9YMDDrmMV25EoYNqi7KUEK57Kn3TCNUJ',
  amount: '0.120087',
  network_fee: '0',
  comment: '',
  created_at: '2014-02-05T13:01:09+01:00',
  txid: '6264fe528116fcb87c812a306ca8409eecfec8fa941546c86f98984b882c8042',
  transferred_at: '2014-02-05T13:05:17+01:00',
  state: 1
}

Parameters:

  • hlx (Hash) —

    transacao uniformizada deposits + withdrawals apide

Returns:

  • (String) —

    valores formatados del (ledger)



93
94
95
96
97
98
99
100
101
 
# File 'lib/cex/bigquery2.rb', line 93

def del_val(hlx)
  "(DATETIME(TIMESTAMP('#{hlx[:time].iso8601}'))," \
  "'#{hlx[:tp]}'," \
  "'#{hlx[:qtxt]}'," \
  "'#{hlx[:id]}'," \
  "cast(#{hlx[:tp] == 'out' ? '-' : ''}#{hlx[:qt]} as numeric)," \
  "cast(#{hlx[:fee]} as numeric)," \
  "#{hlx[:lgid]})"
end

#det_ins ⇒ String

Returns comando insert SQL formatado det (trades).

Returns:

  • (String) —

    comando insert SQL formatado det (trades)



20
21
22
 
# File 'lib/cex/bigquery2.rb', line 20

def det_ins
  "insert #{BD}.det(tp,btc,eur,time,user,id,dtc,dias) VALUES#{apide.trades.map { |h| det_val1(h) }.join(',')}"
end

#det_val1(htx) ⇒ String

Returns valores formatados det (trades parte1).

Examples:

{
  trades: [{
    trade_id: 'XUWWD3',
    trading_pair: 'btceur',
    is_external_wallet_trade: false,
    type: 'sell',
    amount_currency_to_trade: '0.1',
    price: 430,
    volume_currency_to_pay: 43,
    volume_currency_to_pay_after_fee: 42.79,
    amount_currency_to_trade_after_fee: 0.099,
    fee_currency_to_pay: 0.22,
    fee_currency_to_trade: '0.00100000',
    created_at: '2014-03-22T08:14:48+01:00',
    successfully_finished_at: '2014-03-25T14:03:22+01:00',
    state: 1,
    is_trade_marked_as_paid: true,
    trade_marked_as_paid_at: '2014-03-22T08:20:01+01:00',
    payment_method: 1,
    my_rating_for_trading_partner: 'positive',
    trading_partner_information: {
      username: 'emax2000',
      is_kyc_full: false,
      trust_level: 'bronze',
      amount_trades: 4,
      rating: 100,
      bank_name: 'CASSA DI RISPARMIO DI CIVITAVECCHIA SPA',
      bic: 'CRFIIT2CXXX',
      seat_of_bank: 'IT'
    }
  }, {}],
  page: { current: 1, last: 2 },
  errors: [],
  credits: 22
}

Parameters:

  • htx (Hash) —

    transacao trade apide

Returns:

  • (String) —

    valores formatados det (trades parte1)



43
44
45
46
47
48
49
50
51
52
 
# File 'lib/cex/bigquery2.rb', line 43

def det_val1(htx)
  "('#{htx[:type]}'," \
  'cast(' \
  "#{htx[:type] == 'buy' ? htx[:amount_currency_to_trade_after_fee] : "-#{htx[:amount_currency_to_trade]}"}" \
  ' as numeric),' \
  "cast(#{htx[:volume_currency_to_pay_after_fee]} as numeric)," \
  "DATETIME(TIMESTAMP('#{htx[:successfully_finished_at]}'))," \
  "'#{htx[:trading_partner_information][:username]}'," \
  "#{det_val2(htx)}"
end

#det_val2(htx) ⇒ String

Returns valores formatados det (trades parte2).

Parameters:

  • htx (Hash) —

    transacao trade apide

Returns:

  • (String) —

    valores formatados det (trades parte2)



56
57
58
59
60
 
# File 'lib/cex/bigquery2.rb', line 56

def det_val2(htx)
  "'#{htx[:trade_id]}'," \
    "DATETIME(TIMESTAMP('#{htx[:trade_marked_as_paid_at]}'))," \
  "#{Integer(ops[:h][htx[:trade_id]] || 0)})"
end

#dml(cmd) ⇒ Integer

cria Data Manipulation Language (DML) job bigquery

Parameters:

  • cmd (String) —

    comando SQL a executar

Returns:

  • (Integer) —

    numero linhas afetadas



141
142
143
 
# File 'lib/cex/bigquery1.rb', line 141

def dml(cmd)
  job?(cmd) ? 0 : job.num_dml_affected_rows
end

#frl_ins ⇒ String

Returns comando insert SQL formatado fr (ledger).

Returns:

  • (String) —

    comando insert SQL formatado fr (ledger)



30
31
32
 
# File 'lib/cex/bigquery2.rb', line 30

def frl_ins
  "insert #{BD}.fr(uuid,tipo,valor,moe,time,dias) VALUES#{apifr.ledger.map { |h| frl_val(h) }.join(',')}"
end

#frl_val(hlx) ⇒ String

Returns valores formatados frl (ledger).

Examples:

[
  {
    uuid: '50551e61-4e74-4ae7-85fd-9c2040542818',
    currency_amount: nil,
    state: 'executed',
    btc_fee: '0.0',
    currency_fee: '0.0',
    created_at: '2014-03-04T09:00Z',
    updated_at: '2014-03-04T09:00Z',
    currency: 'EUR',
    comment: '5723',
    amount: '100.0',
    type: 'WireDeposit',
    account_operations: [{
      uuid: 'b5058a68-cf99-4438-86d3-e773eba418ec',
      name: 'wire_deposit',
      amount: '100.0',
      currency: 'EUR',
      created_at: '2014-03-04T09:00Z',
      created_at_int: 1_393_923_644,
      is_trading_account: false
    }, {}]
  }, {}
]

Parameters:

  • hlx (Hash) —

    transacao ledger apifr

Returns:

  • (String) —

    valores formatados frl (ledger)



121
122
123
124
125
126
127
128
 
# File 'lib/cex/bigquery2.rb', line 121

def frl_val(hlx)
  "('#{hlx[:uuid]}'," \
  "'#{hlx[:name]}'," \
  "cast(#{hlx[:amount]} as numeric)," \
  "'#{hlx[:currency]}'," \
  "PARSE_DATETIME('%s', '#{hlx[:created_at_int]}')," \
  "#{Integer(ops[:h][hlx[:uuid]] || 0)})"
end

#job?(cmd) ⇒ Boolean

cria job bigquery & verifica execucao

Parameters:

  • cmd (String) —

    comando SQL a executar

Returns:

  • (Boolean) —

    job ok?



121
122
123
124
125
126
 
# File 'lib/cex/bigquery1.rb', line 121

def job?(cmd)
  @job = api.query_job(cmd)
  @job.wait_until_done!
  puts(@job.error['message']) if @job.failed?
  @job.failed?
end

#mostra_tudo ⇒ Object

situacao completa entre kraken/bitcoinde/paymium/therock & bigquery



80
81
82
83
84
85
 
# File 'lib/cex/bigquery1.rb', line 80

def mostra_tudo
  apius.mostra_resumo
  apide.mostra_resumo
  apifr.mostra_resumo
  apimt.mostra_resumo
end

#mtl_ins ⇒ String

Returns comando insert SQL formatado fr (ledger).

Returns:

  • (String) —

    comando insert SQL formatado fr (ledger)



35
36
37
38
 
# File 'lib/cex/bigquery2.rb', line 35

def mtl_ins
  "insert #{BD}.mt(id,time,type,valor,moe,pair,note,trade_id,dias) " \
  "VALUES#{apimt.ledger.map { |h| mtl_val1(h) }.join(',')}"
end

#mtl_val1(hlx) ⇒ String

Returns valores formatados mtl (ledger parte1).

Examples:

{
  transactions: [
    {
      id: 305_445,
      date: '2014-03-06T10:59:13.000Z',
      type: 'withdraw',
      price: 97.47,
      currency: 'EUR',
      fund_id: nil,
      order_id: nil,
      trade_id: nil,
      note: 'BOV withdraw',
      transfer_detail: nil
    },
    {}
  ],
  meta: {
    total_count: nil,
    first: { page: 1, href: 'https://api.therocktrading.com/v1/transactions?page=1' },
    previous: nil,
    current: { page: 1, href: 'https://api.therocktrading.com/v1/transactions?page=1' },
    next: { page: 2, href: 'https://api.therocktrading.com/v1/transactions?page=2' },
    last: nil
  }
}

Parameters:

  • hlx (Hash) —

    transacao ledger apimt

Returns:

  • (String) —

    valores formatados mtl (ledger parte1)



133
134
135
136
137
138
139
140
141
 
# File 'lib/cex/bigquery2.rb', line 133

def mtl_val1(hlx)
  "(#{hlx[:id]}," \
  "DATETIME(TIMESTAMP('#{hlx[:date]}'))," \
  "'#{hlx[:type]}'," \
  "cast(#{hlx[:price]} as numeric)," \
  "'#{hlx[:currency]}'," \
  "#{hlx[:fund_id].to_s.empty? ? 'null' : "'#{hlx[:fund_id]}'"}," \
  "#{mtl_val2(hlx)}"
end

#mtl_val2(hlx) ⇒ String

Returns valores formatados mtl (ledger parte2).

Parameters:

  • hlx (Hash) —

    transacao ledger apimt

Returns:

  • (String) —

    valores formatados mtl (ledger parte2)



145
146
147
148
149
 
# File 'lib/cex/bigquery2.rb', line 145

def mtl_val2(hlx)
  "#{hlx[:note].to_s.empty? ? 'null' : "'#{hlx[:note]}'"}," \
  "#{hlx[:trade_id].to_s.empty? ? 'null' : (hlx[:trade_id]).to_s}," \
  "#{Integer(ops[:h][String(hlx[:id])] || 0)})"
end

#processa_de ⇒ Object

insere transacoes bitcoinde novas nas tabelas det (trades), del (ledger)



102
103
104
105
 
# File 'lib/cex/bigquery1.rb', line 102

def processa_de
  puts(format("%<n>2i TRADES BITCOINDE INSERIDAS #{BD}.det", n: apide.trades.empty? ? 0 : dml(det_ins)))
  puts(format("%<n>2i LEDGER BITCOINDE INSERIDAS #{BD}.del", n: apide.ledger.empty? ? 0 : dml(del_ins)))
end

#processa_fr ⇒ Object

insere transacoes paymium novas na tabela fr (ledger)



108
109
110
 
# File 'lib/cex/bigquery1.rb', line 108

def processa_fr
  puts(format("%<n>2i LEDGER PAYMIUM INSERIDAS #{BD}.fr", n: apifr.ledger.empty? ? 0 : dml(frl_ins)))
end

#processa_mt ⇒ Object

insere transacoes paymium novas na tabela mt (ledger)



113
114
115
 
# File 'lib/cex/bigquery1.rb', line 113

def processa_mt
  puts(format("%<n>2i LEDGER THEROCK INSERIDAS #{BD}.mt", n: apimt.ledger.empty? ? 0 : dml(mtl_ins)))
end

#processa_tudo ⇒ Object

insere (caso existam) transacoes novas do kraken/bitcoinde/paymium/therock no bigquery



88
89
90
91
92
93
 
# File 'lib/cex/bigquery1.rb', line 88

def processa_tudo
  processa_us
  processa_de
  processa_fr
  processa_mt
end

#processa_us ⇒ Object

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



96
97
98
99
 
# File 'lib/cex/bigquery1.rb', line 96

def processa_us
  puts(format("%<n>2i TRADES KRAKEN INSERIDAS #{BD}.ust", n: apius.trades.empty? ? 0 : dml(ust_ins)))
  puts(format("%<n>2i LEDGER KRAKEN INSERIDAS #{BD}.usl", n: apius.ledger.empty? ? 0 : dml(usl_ins)))
end

#sql(cmd, res = []) ⇒ Google::Cloud::Bigquery::Data

cria Structured Query Language (SQL) job bigquery

Parameters:

  • cmd (String) —

    comando SQL a executar

  • res (String) (defaults to: []) —

    resultado quando SQL tem erro

Returns:

  • (Google::Cloud::Bigquery::Data) —

    resultado do SQL



133
134
135
 
# File 'lib/cex/bigquery1.rb', line 133

def sql(cmd, res = [])
  @sqr = job?(cmd) ? res : job.data
end

#usl_ins ⇒ String

Returns comando insert SQL formatado usl (ledger).

Returns:

  • (String) —

    comando insert SQL formatado usl (ledger)



14
15
16
17
 
# File 'lib/cex/bigquery2.rb', line 14

def usl_ins
  "insert #{BD}.usl(txid,refid,time,type,aclass,asset,amount,fee) " \
  "VALUES#{apius.ledger.map { |k, v| usl_val(k, v) }.join(',')}"
end

#usl_val(idx, hlx) ⇒ String

Returns valores formatados usl (ledger).

Examples:

{
  error: [],
  result: {
    ledger: {
      "LXXURB-ITI7S-CXVERS": {
        refid: 'ACCHF3A-RIBBMO-VYBESY',
        time: 1_543_278_716.2775,
        type: 'withdrawal',
        subtype: '',
        aclass: 'currency',
        asset: 'ZEUR',
        amount: '-15369.6200',
        fee: '0.0900',
        balance: '0.0062'
      },
      "OUTRO-LEDGER-ID": {}
    },
    count: 376
  }
}

Parameters:

  • hlx (Hash) —

    transacao ledger apius

  • idx (String) —

    identificador transacao

Returns:

  • (String) —

    valores formatados usl (ledger)



107
108
109
110
111
112
113
114
115
116
 
# File 'lib/cex/bigquery2.rb', line 107

def usl_val(idx, hlx)
  "('#{idx}'," \
  "'#{hlx[:refid]}'," \
  "PARSE_DATETIME('%s', '#{String(hlx[:time].round)}')," \
  "'#{hlx[:type]}'," \
  "#{hlx[:aclass].to_s.empty? ? 'null' : "'#{hlx[:aclass]}'"}," \
  "'#{hlx[:asset]}'," \
  "cast(#{hlx[:amount]} as numeric)," \
  "cast(#{hlx[:fee]} as numeric))"
end

#ust_ins ⇒ String

Returns comando insert SQL formatado ust (trades).

Returns:

  • (String) —

    comando insert SQL formatado ust (trades)



8
9
10
11
 
# File 'lib/cex/bigquery2.rb', line 8

def ust_ins
  "insert #{BD}.ust(txid,ordertxid,pair,time,type,ordertype,price,cost,fee,vol,margin,misc,ledgers,dias) " \
  "VALUES#{apius.trades.map { |k, v| ust_val1(k, v) }.join(',')}"
end

#ust_val1(idx, htx) ⇒ String

Returns valores formatados ust (trades parte1).

Examples:

{
  error: [],
  result: {
    trades: {
      "TVINF5-TIOUB-YFNGKE": {
        ordertxid: 'ORPSUW-YKP4F-UJZOC6',
        pair: 'XETHXXBT',
        time: 1_463_435_684.8387,
        type: 'buy',
        ordertype: 'market',
        price: '0.024989',
        cost: '1.193973',
        fee: '0.003104',
        vol: '47.77994129',
        margin: '0.000000',
        misc: ''
      },
      "OUTRO-TRADE-ID": {}
    },
    count: 157
  }
}

Parameters:

  • idx (String) —

    identificador transacao

  • htx (Hash) —

    transacao trade apius

Returns:

  • (String) —

    valores formatados ust (trades parte1)



66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/cex/bigquery2.rb', line 66

def ust_val1(idx, htx)
  "('#{idx}'," \
  "'#{htx[:ordertxid]}'," \
  "'#{htx[:pair]}'," \
  "PARSE_DATETIME('%s', '#{String(htx[:time].round)}')," \
  "'#{htx[:type]}'," \
  "'#{htx[:ordertype]}'," \
  "cast(#{htx[:price]} as numeric)," \
  "cast(#{htx[:cost]} as numeric)," \
  "cast(#{htx[:fee]} as numeric)," \
  "#{ust_val2(idx, htx)}"
end

#ust_val2(idx, htx) ⇒ String

Returns valores formatados ust (trades parte2).

Parameters:

  • idx (String) —

    identificador transacao

  • htx (Hash) —

    transacao trade apius

Returns:

  • (String) —

    valores formatados ust (trades parte2)



81
82
83
84
85
86
87
 
# File 'lib/cex/bigquery2.rb', line 81

def ust_val2(idx, htx)
  "cast(#{htx[:vol]} as numeric)," \
  "cast(#{htx[:margin]} as numeric)," \
  "#{htx[:misc].to_s.empty? ? 'null' : "'#{htx[:misc]}'"}," \
  "'#{apius.ledger.select { |_, v| v[:refid] == idx }.keys.join(',') || ''}'," \
  "#{Integer(ops[:h][idx] || 0)})"
end