Query ENS and 0x events with SQL in Google BigQuery


Query ENS and 0x contract events with SQL in Google BigQuery

  • You can now easily query parsed ENS and 0x smart contract events in Google BigQuery: 0x tables, ENS tables. Those tables are updated daily.
  • You can easily add events for any Ethereum contract you are interested in to public blockchain-etl datasets. Find instructions below.

Accessing Datasets in BigQuery

After you open the dataset links (0x tables, ENS tables) in your browser you should be able to see the blockchain-etl project and the available datasets within it on the left:

You can select each table and view its schema, details, and preview the data on the bottom right.

Try pasting this query to the editor on the top right and click the Run button to query the ETHRegistrarController_event_NameRegistered table:

select name, owner, cost
from `blockchain-etl.ethereum_ens.ETHRegistrarController_event_NameRegistered`
order by cost desc
limit 10

Adding Your Datasets and Tables

You can easily add events and function calls for any Ethereum contract you are interested in to public BigQuery datasets. All you need to do is to create a pull request with table definitions to ethereum-etl-airflow repository. Create a directory for your dataset and a table definition file for each event or function call in the Ethereum contracts you are interested in.

Let’s take a look at the table definition file for NameRegistered event in the ENS Registrar Controller contract 0xf0ad5cad05e10572efceb849f6ff0c68f9700455:

There are 2 parts to the table definition file: the parser and the table. The event abi for the parser can be taken from Etherscan’s contract tab. The table schema should include all fields defined in the event/function abi.

The new tables will be publicly available in the blockchain-etl datasets in BigQuery the next day after your pull request is merged. Go ahead and fork the repo ethereum-etl-airflow and add a dataset for your favorite Ethereum DApp.

Follow us on Twitter: https://twitter.com/EthereumETL.

Query ENS and 0x events with SQL in Google BigQuery was originally published in Coinmonks on Medium, where people are continuing the conversation by highlighting and responding to this story.