Are you ready to dive deep into the world of Ethereum blockchain data using SQL? With the Presto Ethereum Connector, you can run interactive queries and analyze blockchain information with ease. Let’s walk through how you can set up and utilize this powerful tool!

Introduction

Presto is an interactive SQL query engine that allows you to run SQL queries on various data sources like MySQL, HDFS, and now, the Ethereum blockchain! This connector helps you access Ethereum blockchain analytics without needing to grapple with the complex JavaScript API. Ready to get started? Let’s go!

Prerequisites

Before you embark on your querying journey, you need to ensure you have an Ethereum client. Here are your options:

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

Setting Up the Presto Ethereum Connector

Follow these steps to install and configure your Presto Ethereum connector. Think of it as building a house — each step lays the foundation for the next!

1. Install Presto. Make sure to create the relevant configuration files.

   Your Presto 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. You’ll find the plugin in the target folder.

4. Load the plugin into Presto:

   • Create the Ethereum connector config inside of etc.

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

   Paste the following into ethereum.properties:

   connector.name=ethereum
   ethereum.jsonrpc=http://localhost:8545

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

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

   Your final Presto installation folder structure should look like this:

   • bin
   • lib
   • etc
   • catalog
   • plugin
   • ethereum

Running Queries

Now, you are ready to start the Presto server and run some queries:

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

Use Cases

To give you an idea of the power at your fingertips, here are a few example queries:

• 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;

• Average block time (every 200th block from genesis to block 10000):

  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(1000) OVER (ORDER BY bn) AS chunk, * FROM X) AS T
  GROUP BY chunk
  ORDER BY chunkStart;

• Biggest miners in first 100k 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;

Understanding Database Structures

To view the database structure, you can use the following commands:

SHOW TABLES;

DESCRIBE block;

DESCRIBE transaction;

DESCRIBE erc20;

Utilizing Web3 Functions

In addition to various built-in Presto functions, several Web3 functions can be called inline with SQL statements. Here are the supported functions:

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

Troubleshooting

While setting up your environment, you might face some challenges. Here are some troubleshooting ideas:

• If you encounter syntax errors, ensure you are using Python 2. Using Python 3 can lead to issues, such as:

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

• Make sure to use Java 8 only. If you use another version, you might see these errors:

Unrecognized VM option ExitOnOutOfMemoryError  
Error: Could not create the Java Virtual Machine.

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

Conclusion

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.