当前位置: 首页 > 软件库 > Web3 > 区块链 >

presto-ethereum

Presto Ethereum Connector -- SQL on Ethereum
授权协议 Apache-2.0 License
开发语言
所属分类 Web3、 区块链
软件类型 开源软件
地区 不详
投 递 者 澹台成龙
操作系统 跨平台
开源组织
适用人群 未知
 软件概览

Presto Ethereum Connector

Unleash the Power of Presto Interactive SQL Querying on Ethereum Blockchain

Introduction

Presto is a powerful interactive querying engine that enables running SQL queries on anything -- be it MySQL, HDFS, local file, Kafka -- as long as there exist a connector to the source.

This is a Presto connector to the Ethereum blockchain data. With this connector, one can get hands on with Ethereum blockchain analytics work without having to know how to play with the nitty gritty Javascript API.

Prerequisites

Have an Ethereum client that you can connect to. There are 2 options:

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

Note

Specify a block range where you can (e.g. WHERE block.block_number > x AND block.block_number < y, or WHERE transaction.tx_blocknumber > x AND transaction.tx_blocknumber < y, or WHERE erc20.erc20_blocknumber > x AND erc20.erc20_blocknumber < y). Block number is the default and only predicate that can push down to narrow down data scan range. Queries without block ranges will cause presto to retrieve blocks all the way from the first block, which takes forever.

Usage

  1. Install Presto. Follow the instructions on that page to create relevant config files.
    By the end of this step, your presto installation folder structure should look like:

    ├── bin
    ├── lib
    ├── etc
    │   ├── config.properties
    │   ├── jvm.config
    │   └── node.properties
    ├── plugin
    
  2. Install Presto CLI

  3. Clone this repo and run mvn clean package to build the plugin. You will find the built plugin in the target folder.

  4. Load the plugin to Presto
    a. Create the ethereum connector config inside of etc.
    $ mkdir -p etc/catalog && touch etc/catalog/ethereum.properties
    Paste the following to the ethereum.properties:

    connector.name=ethereum
    
    # You can connect through Ethereum HTTP JSON RPC endpoint
    # IMPORTANT - for local testing start geth with rpcport
    # geth --rpc --rpcaddr "127.0.0.1" --rpcport "8545"
    ethereum.jsonrpc=http://localhost:8545/
    
    
    # Or you can connect through IPC socket
    # ethereum.ipc=/path/to/ipc_socketfile
    
    # Or you can connect to Infura
    # ethereum.infura=https://mainnet.infura.io/<your_token>
    

    b. 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
    

    By the end of this step, your presto installation folder structure should look like:

    ├── bin
    ├── lib
    ├── etc
    │   ├── catalog
    │   │   └── ethereum.properties
    │   ├── config.properties
    │   ├── jvm.config
    │   └── node.properties
    ├── plugin
    │   ├── ethereum
    │   │   └── <some jars>
    
  5. There you go. You can now start the presto server, and query through presto-cli:

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

Use Cases

Inspired by An Analysis of the First 100000 Blocks, the following SQL queries capture partially what was depicted in that post.

  • 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(10000/200) OVER (ORDER BY bn) AS chunk, * FROM X) AS T
GROUP BY chunk
ORDER BY chunkStart;
  • Biggest miners in first 100k blocks (address, 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;
  • ERC20 Token Movement in the last 100 blocks
SELECT erc20_token, SUM(erc20_value) FROM erc20
WHERE erc20_blocknumber >= 4147340 AND erc20_blocknumber<=4147350
GROUP BY erc20_token;
  • Describe the database structure
SHOW TABLES;
    Table
-------------
 block
 erc20
 transaction

DESCRIBE block;
Column                 | Type               | Extra | Comment
-----------------------------------------------------------
block_number           | bigint             |       |
block_hash             | varchar(66)        |       |
block_parenthash       | varchar(66)        |       |
block_nonce            | varchar(18)        |       |
block_sha3uncles       | varchar(66)        |       |
block_logsbloom        | varchar(514)       |       |
block_transactionsroot | varchar(66)        |       |
block_stateroot        | varchar(66)        |       |
block_miner            | varchar(42)        |       |
block_difficulty       | bigint             |       |
block_totaldifficulty  | bigint             |       |
block_size             | integer            |       |
block_extradata        | varchar            |       |
block_gaslimit         | double             |       |
block_gasused          | double             |       |
block_timestamp        | bigint             |       |
block_transactions     | array(varchar(66)) |       |
block_uncles           | array(varchar(66)) |       |


DESCRIBE transaction;

Column              |    Type     | Extra | Comment
--------------------------------------------------
tx_hash             | varchar(66) |       |
tx_nonce            | bigint      |       |
tx_blockhash        | varchar(66) |       |
tx_blocknumber      | bigint      |       |
tx_transactionindex | integer     |       |
tx_from             | varchar(42) |       |
tx_to               | varchar(42) |       |
tx_value            | double      |       |
tx_gas              | double      |       |
tx_gasprice         | double      |       |
tx_input            | varchar     |       |


DESCRIBE erc20;
      Column       |    Type     | Extra | Comment
-------------------+-------------+-------+---------
 erc20_token       | varchar     |       |
 erc20_from        | varchar(42) |       |
 erc20_to          | varchar(42) |       |
 erc20_value       | double      |       |
 erc20_txhash      | varchar(66) |       |
 erc20_blocknumber | bigint      |       |

Web3 Functions

In addition to the various built-in Presto functions, some web3 functions are ported so that they can be called inline with SQL statements directly. Currently, the supported web3 functions are

  1. fromWei
  2. toWei
  3. eth_gasPrice
  4. eth_blockNumber
  5. eth_getBalance
  6. eth_getTransactionCount

Troubleshooting

  • You must use python2. You will get invalid syntax errors if you use Python3.
-> bin/launcher start
  File "/your_path/presto-server-0.196/bin/launcher.py", line 38
    except OSError, e:
                  ^
SyntaxError: invalid syntax
  • Use Java 8 only. You might get the following errors if you use the wrong Java version.
Unrecognized VM option 'ExitOnOutOfMemoryError'
Did you mean 'OnOutOfMemoryError=<value>'?
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.
 相关资料
  • Presto是一个由Opera Software开发的浏览器排版引擎(非开源),供Opera 7.0~10.00版使用。 Presto取代了旧版Opera 4至6版本使用的Elektra排版引擎,包括加入动态功能,例如网页或其部分可随着DOM及Script语法的事件而重新排版。 Presto在推出后不断有更新版本推出,使不少错误得以修正,以及阅读Javascript效能得以最佳化。

  • 我们注意到在我们的presto集群(安装在Linux机器上的presto)中有以下奇怪的行为 我们有9台presto workers机器, 从presto仪表板中,我们可以看到,有时有活动的工作者,有时所有presto工作者- 从presto workers的日志中我看不出有什么不寻常的地方 我不确定我们是否需要搜索任何网络问题或其他问题? 注意:当我重新启动所有的presto workers时,

  • 是否有任何选项可以在Presto cli上提供参数。

  • 但当我从presto查询时,我得到以下错误:

  • 问题内容: 我想知道AWS Athena中是否支持OFFSET。对于mysql,以下查询正在运行,但在雅典娜中却给了我错误。任何示例都将有所帮助。 从雇员中选择* empSal> 3000 LIMIT 300 OFFSET 20 问题答案: 雅典娜基本上是由Presto管理的。从Presto311开始,您可以使用语法或等效的ANSI SQL :。 您可以在超越极限中阅读更多内容,Presto满足O

  • 我使用Presto Cli测试ldap,下面是以下命令: 它不要求密码,我能够连接到Presto集群,并能够运行查询。为什么LDAP身份验证对此没有任何帮助?