Anyblock Analytics is now Blockdaemon Germany! Read the news under our blog!
Aug 13, 2020

Basic SQL Usage Examples

by

Icon article header

Welcome!

Within this tutorial you will learn how to retrieve and analyze data from the Ethereum Blockchain with the help of the anyblock.tools SQL interface.

We will show you how to retrieve data from anyblock.tools using common SQL language.

What you must know already

This tutorial is written for programmers, who have some experience with SQL. You should also have visited the authorization page and setup our connection to the database. Remember that the subentities are stored using the Postgresql-Type JSONB. For more information on that, please take a look at the ER model.

Basic anyblock.tools SQL queries

Choose one of the databases available. All of them are encoded as the triple of:

<technology>_<chain>_<network></network></chain></technology>

The main chain is named ethereum_ethereum_mainnet.

After choosing a blockchain, you might continue with the example queries.

Find the latest block

SELECT * FROM block ORDER BY number DESC LIMIT 1

This will show the whole block. But you can use a shorter form:

SELECT max(number) FROM block

Find events for a given block

SELECT * FROM event
WHERE event.block_number=7075271

Find calls for a transaction hash

SELECT * FROM call
WHERE call.hash = '0xadd837afa5b68987eb9f0167ad65cbb8131f57da84db56a19acf4a5a98bd35da'

Find transactions for a given contract

For our example we use the address of the TenXPay token contract:

SELECT * FROM tx
WHERE tx.to='0xB97048628DB6B661D4C2aA833e95Dbe1A905B280'
LIMIT 100

Find specific events for a given contract

For our example we use the address of the TenXPay token contract again, however we would like to know the values of transfers greater than 1ETH:


SELECT arg->'scaled', arg->'num'
FROM "event",jsonb_array_elements(args) arg
WHERE event = 'Transfer' AND address = '0xB97048628DB6B661D4C2aA833e95Dbe1A905B280'
AND (arg->'num')::numeric > 1000000000000000000
LIMIT 100

Find the latest 10 Dai Transfer events and extract sender, receiver and value from JSON

SELECT
      *,
      args->0->>'hex' as "from",
      args->1->>'hex' as "to",
      CAST(args->2->'scaled' AS NUMERIC) AS "value"
FROM event
WHERE address = '0x89d24A6b4CcB1B6fAA2625fE562bDD9a23260359'
AND event = 'Transfer'
ORDER BY timestamp DESC
LIMIT 10

Where to go from here

You may continue with taking a look at the Elasticsearch tutorial. Please let us know if you have any further questions or need some help with your application.

Interested or questions?

 

Sascha Göbel
(Co-Founder & Chief Technology Officer)
sascha@anyblockanalytics.com
+49 6131 3272372

    

Looking to create an Anyblock account?

Takes seconds and it's free!

Recent Posts

Anyblock Multi-Chain REST API for RPC & Data

Anyblock Multi-Chain REST API for RPC & Data

Anyblock REST API endpoints for RPC & data are a fast and reliable solution, which is available for more than 20 blockchains and does not require running your own node. You can view the list and description of all our endpoints in the Anyblock REST API docs.

In this article, you will find general information on Anyblock API and examples showing how to interact with it.

November Twitter Summary

November Twitter Summary

Hey there, developers! Anyblock Twitter Summary for the month of November is here.  Read it here or check out our Twitter account.Enjoy it!  Or just follow us directly if it's easier for you. Happy tweet-reading! :)? Most people believe they need to operate a...

This is the beginning of a new chapter…

This is the beginning of a new chapter…

We are super excited to announce that Anyblock Analytics GmbH will be spearheading the expansion of Blockdaemon Inc. in Germany! The corresponding contracts have been signed last week and closing is expected shortly as well.

Pin It on Pinterest