Kiba — Loading Data To Buy & Defi

In my last post, I showed how I collected Earn information from Binance US’s website using Mechanize. Now with the data captured, how do I load it to a database that my website, buyanddefi.com, can use?

What is KIBA?

Kiba is an ETL framework written in ruby that makes it simple to:

• Extract information from a source, such as a database or file
• Transform that information
• Load the updated information to a new destination (database, file, etc)

require 'kiba'
require_relative 'lib/binance'
require_relative 'lib/load_binance'

# Defining an ETL job to retrieve and load Binance information

get_binance = Kiba.parse do
  source Binance
  transform BinanceTransform
  destination LoadBinance
end

Kiba.run(get_binance)

Source (aka Extract)

My page scraper for Binance retrieved information from their website and stored it in an array. To define a source in Kiba, I created a class called Binance which contains 2 methods, initialize and each.

My initialize method is basically the entire mechanize code that visits Binance’s website and stores the data in an array. The each method will read each element of the array and pass it downstream to the transform/destination parts of the ETL job defined by Kiba. Ideally, each element of the array should be a hash.

class Binance

  def initialize
    a = Mechanize.new { |agent|
      agent.user_agent = 'Windows Chrome'
    }
    page = a.get('https://www.binance.us/staking')
    cards = page.search('//*[@id="__APP"]/div/div[1]/div/div[2]/div/div/div/div/child::*')
    @binance = []
    cards.each do | card |
      coin = {}
      coin[:symbol] = card.element_children[0].element_children[0].elements.children.children[0].text
      coin[:name] = card.element_children[0].element_children[0].elements.children.children[1].text
      coin[:rate] = card.element_children[0].element_children[0].elements.children.children[2].text[/(.*)%/,1]
      @binance << coin
    end

  end

  def each
    @binance.each do | coin |
      yield(coin)
    end
  end

end

Transform

This part of the ETL job will allow us to update each “row” of data so that it can be loaded to the destination. The data we received from the scraper, we want to do 2 things:

1. Put the name in all capital letters
2. Add a new key called exchange with the value “BINANCE”

To do this, I created a new class called BinanceTransform.

class BinanceTransform
  def initialize

  end

  def process(row)
    row[:name] = row[:name].upcase
    row[:exchange] = 'BINANCE'
    return row
  end

end

When I run the ETL job, I can see that my extracted data did indeed get transformed as:

{:symbol=>"ETH", :name=>"ETHEREUM", :rate=>"5.00", :exchange=>"BINANCE"}
{:symbol=>"BNB", :name=>"BNB", :rate=>"2.50", :exchange=>"BINANCE"}
{:symbol=>"ADA", :name=>"CARDANO", :rate=>"4.30", :exchange=>"BINANCE"}
{:symbol=>"MATIC", :name=>"POLYGON", :rate=>"6.40", :exchange=>"BINANCE"}
{:symbol=>"VET", :name=>"VECHAIN", :rate=>"2.00", :exchange=>"BINANCE"}
{:symbol=>"SOL", :name=>"SOLANA", :rate=>"7.70", :exchange=>"BINANCE"}
{:symbol=>"ATOM", :name=>"COSMOS", :rate=>"15.00", :exchange=>"BINANCE"}
{:symbol=>"ONE", :name=>"HARMONY", :rate=>"7.20", :exchange=>"BINANCE"}
{:symbol=>"AVAX", :name=>"AVALANCHE", :rate=>"5.40", :exchange=>"BINANCE"}
{:symbol=>"DOT", :name=>"POLKADOT", :rate=>"10.30", :exchange=>"BINANCE"}
{:symbol=>"NEAR", :name=>"NEAR PROTOCOL", :rate=>"7.00", :exchange=>"BINANCE"}
{:symbol=>"ALGO", :name=>"ALGORAND", :rate=>"6.80", :exchange=>"BINANCE"}
{:symbol=>"FTM", :name=>"FANTOM ", :rate=>"3.40", :exchange=>"BINANCE"}
{:symbol=>"GRT", :name=>"THE GRAPH", :rate=>"6.70", :exchange=>"BINANCE"}
{:symbol=>"XTZ", :name=>"TEZOS ", :rate=>"3.00", :exchange=>"BINANCE"}
{:symbol=>"ROSE", :name=>"OASIS NETWORK", :rate=>"4.50", :exchange=>"BINANCE"}
{:symbol=>"FLOW", :name=>"FLOW", :rate=>"7.00", :exchange=>"BINANCE"}
{:symbol=>"TRX", :name=>"TRON", :rate=>"4.90", :exchange=>"BINANCE"}
{:symbol=>"AUDIO", :name=>"AUDIUS", :rate=>"12.80", :exchange=>"BINANCE"}
{:symbol=>"LPT", :name=>"LIVEPEER", :rate=>"11.40", :exchange=>"BINANCE"}
{:symbol=>"FET", :name=>"FETCH.AI", :rate=>"3.60", :exchange=>"BINANCE"}
{:symbol=>"KSM", :name=>"KUSAMA", :rate=>"11.80", :exchange=>"BINANCE"}
{:symbol=>"CELR", :name=>"CELER", :rate=>"4.80", :exchange=>"BINANCE"}
{:symbol=>"BAND", :name=>"BAND PROTOCOL", :rate=>"12.00", :exchange=>"BINANCE"}
{:symbol=>"SKL", :name=>"SKALE", :rate=>"7.00", :exchange=>"BINANCE"}
{:symbol=>"T", :name=>"THRESHOLD", :rate=>"5.50", :exchange=>"BINANCE"}

Destination

To load the newly transformed data into the Buy And Defi database, I created a new called LoadBinance which consists of three methods

• initialize — open a connection to the database
• write — insert/update the record in the appropriate table
• close — close the database connection

class LoadBinance

  def initialize
    @conn = PG.connect( :host => ENV['HOST'], :dbname=> ENV['DB'], :user => ENV['DBUSER'], :password => ENV['DBPASS'], :sslmode=> ENV['SSLMODE'])
  end

  def write(row)
    @conn.exec_params("insert into coins ( symbol, name, rate, exchange ) values ( $1, $2, $3, $4) on conflict (name, exchange) do update set rate = $3, updated_at = NOW()",
                      [ row[:symbol], row[:name], row[:rate], row[:exchange] ])
  end

  def close
    @conn.close
  end

end

Note: Because the my ETL job will run on a daily basis, my insert query in the write method includes an on conflict condition.

Kiba.run(get_binance)

With all the classes defined, I can simply have Github run the Kiba job(s) and voila! 26 rows have been added to my database.

Screenshot of database (interface courtesy bit.io)

My ETL job, get_binance, can now run on a daily basis and update the Buy And Defi database for any rate changes or new coins added on Binance’s Earn page.

In my next article, I’ll share how I configured a Github action to run the ETL job on a daily basis.