Introduction

Welcome to the world of Presto, a powerful interactive querying engine that lets you run SQL queries on various data sources. With the new Presto connector for Ethereum blockchain data, diving into Ethereum analytics has never been easier! Say goodbye to wrestling with complex JavaScript APIs and hello to querying blockchain data using familiar SQL syntax.

Prerequisites

Before you embark on this querying adventure, ensure you have access to an Ethereum client. Here are your options:

• Run Geth or Parity locally.
• Utilize Infura, a hosted Ethereum client in the cloud.

Getting Started

Before diving into usage, pay attention to the importance of specifying a block range in your queries. Failing to do this may result in lengthy retrieval times as Presto will attempt to fetch data from the very first block!

Usage

To set up your environment, follow these steps carefully:

1. Install Presto by following the instructions on that page. Your installation folder structure should look like this:

bin
lib
etc
    config.properties
    jvm.config
    node.properties
plugin

2. Install Presto CLI.
3. Clone the repository and run mvn clean package to build the plugin. The built plugin will be found in the target folder.
4. Load the plugin into Presto:
• Create the Ethereum connector config inside the etc directory:

$ mkdir -p etc/catalog  
$ touch etc/catalog/ethereum.properties

• Paste the following lines into ethereum.properties:

connector.name=ethereum
ethereum.jsonrpc=http://localhost:8545
# For Infura, uncomment the line below and specify your token
# ethereum.infura=https://mainnet.infura.io/v3/your_token

• Copy and extract the built plugin to your Presto plugin folder:

$ mkdir -p plugin/ethereum  
$ cp path_to_this_repotarget/presto-ethereum-*-plugin.tar.gz .  
$ tar xfz presto-ethereum-*-plugin.tar.gz -C plugin/ethereum --strip-components=1

• After these steps, your folder structure should resemble the following:

bin
lib
etc
    catalog
        ethereum.properties
    config.properties
    jvm.config
    node.properties
plugin
    ethereum
        some jars

• Finally, start the Presto server and query using Presto CLI:

$ bin/launcher start  
$ presto-cli --server localhost:8080 --catalog ethereum --schema default

Use Cases

Explore the power of SQL queries with the following examples:

• Find the first 50 block times (in seconds):

SELECT b.bn, (b.block_timestamp - a.block_timestamp) AS delta
FROM    (SELECT block_number AS bn, block_timestamp
FROM block WHERE block_number=1 AND block_number=50) AS a
JOIN    (SELECT (block_number-1) AS bn, block_timestamp
FROM block WHERE block_number=2 AND block_number=51) AS b
ON a.bn=b.bn
ORDER BY b.bn;

• Calculate the average block time every 200th block up to block 10,000:

WITH X AS (SELECT b.bn, (b.block_timestamp - a.block_timestamp) AS delta
FROM    (SELECT block_number AS bn, block_timestamp
FROM block WHERE block_number=1 AND block_number=10000) AS a
JOIN    (SELECT (block_number-1) AS bn, block_timestamp
FROM block WHERE block_number=2 AND block_number=10001) AS b
ON a.bn=b.bn
ORDER BY b.bn)
SELECT min(bn) AS chunkStart, avg(delta)
FROM    (SELECT ntile(10000200) OVER (ORDER BY bn) AS chunk, * FROM X) AS T
GROUP BY chunk
ORDER BY chunkStart;

• Identify the biggest miners in the first 100,000 blocks:

SELECT block_miner, count(*) AS num, count(*)/100000.0 AS PERCENT
FROM block
WHERE block_number=100000
GROUP BY block_miner
ORDER BY num DESC
LIMIT 15;

Web3 Functions

This connector supports several web3 functions that can be called inline with SQL statements:

• fromWei
• toWei
• eth_gasPrice
• eth_blockNumber
• eth_getBalance
• eth_getTransactionCount

Troubleshooting

If you encounter issues, here are some troubleshooting tips:

• Be sure to use Python 2. Using Python 3 will generate syntax errors. For example:

File your_path/presto-server-0.196/bin/launcher.py, line 38
except OSError, e:
              ^SyntaxError: invalid syntax

• Stick to Java 8 only. Other versions may yield errors like:

Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

• For more insights, updates, or to collaborate on AI development projects, stay connected with fxis.ai.

At fxis.ai, we believe that such advancements are crucial for the future of AI, as they enable more comprehensive and effective solutions. Our team is continually exploring new methodologies to push the envelope in artificial intelligence, ensuring that our clients benefit from the latest technological innovations.