在以太坊区块链上释放Presto交互式SQL查询的功能
Presto是一个功能强大的交互式查询引擎，只要存在与源的连接器，它就可以对任何内容（包括MySQL，HDFS，本地文件，Kafka）运行SQL查询。
这是与以太坊区块链数据的Presto连接器。有了这个连接器，您就可以开始进行以太坊区块链分析工作，而不必知道如何使用精巧的Javascript API。
有一个可以连接的以太坊客户端。有2个选项：
在本地运行Geth或Parity。使用Infura（云中托管的以太坊客户端）。指定一个块范围在这里可以（例如WHERE block.block_number > x AND block.block_number < y，或WHERE transaction.tx_blocknumber > x AND transaction.tx_blocknumber < y，或WHERE erc20.erc20_blocknumber > x AND erc20.erc20_blocknumber < y）。块号是默认值，并且是唯一可以下推以缩小数据扫描范围的谓词。没有块范围的查询将导致presto从第一个块一直检索块，这将花费很长时间。
安装Presto。按照该页面上的说明创建相关的配置文件。 在此步骤结束时，您的presto安装文件夹结构应如下所示：
├── bin ├── lib ├── etc │ ├── config.properties │ ├── jvm.config │ └── node.properties ├── plugin安装Presto CLI
克隆此仓库并运行mvn clean package以构建插件。您将在target文件夹中找到内置的插件。
将插件加载到Presto a。在中创建以太坊连接器配置etc。$ mkdir -p etc/catalog && touch etc/catalog/ethereum.properties 将以下内容粘贴到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。将构建的插件复制并解压缩到presto插件文件夹中
$ 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在此步骤结束时，您的presto安装文件夹结构应如下所示：
├── bin ├── lib ├── etc │ ├── catalog │ │ └── ethereum.properties │ ├── config.properties │ ├── jvm.config │ └── node.properties ├── plugin │ ├── ethereum │ │ └── <some jars>妳去 现在，您可以启动presto服务器，并通过presto-cli查询：
$ bin/launcher start $ presto-cli --server localhost:8080 --catalog ethereum --schema default受前100000块分析的启发，以下SQL查询部分捕获了该文章中描述的内容。
前50个区块时间（以秒为单位） 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;平均区块时间（从创始到区块10000的第200个区块）
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;前10万个区块中最大的矿工（地址，区块，％）
ELECT 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 ;最近100个区块中的ERC20代币移动 SELECT erc20_token，SUM（erc20_value）FROM erc20 WHERE erc20_blocknumber > = 4147340 AND erc20_blocknumber <= 4147350 GROUP BY erc20_token;
描述数据库结构
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 | |除了各种内置的Presto函数之外，还移植了一些web3函数，以便可以直接与SQL语句内联调用它们。当前，支持的web3功能是
来自魏到魏eth_gas价格eth_blockNumbereth_getBalanceeth_getTransactionCount源码地址：https://github.com/xiaoyao1991/presto-ethereum
