SQL
Implementation
Data is stored within the MySQL database. The appropriate dialect must be used when building SQL queries.
Example
Let's build the same chart that was given as an example in , but with SQL now.
- Click on
Create
and selectSQL query
- Enter the following SQL query
SELECT
`ethereum_transactions`.`block_date` AS `block_date`,
count(*) AS `count`
FROM
`ethereum_transactions`
GROUP BY
`ethereum_transactions`.`block_date`
ORDER BY
`ethereum_transactions`.`block_date` ASC
- Choose a
Line chart
as a visualisation option
Best practices
Good sql habits can improve the efficiency of running SQL, hope these cases can help you optimise your sql code and find the data you want faster.
WHERE
clause
WHERE
clause🔴 Not recommended
date(block_timestamp) > '2022-03-01'
🟢 Recommended
block_timestamp > CAST ('2022-03-01' AS datetime)
Number of results
Try to LIMIT
the time and order of magnitude of large tables at the beginning, filter only the required fields, and try to avoid full table scans.
🔴 Not recommended
SELECT *
FROM TABLE
🟢 Recommended
SELECT name,
on_date
FROM TABLE
LIMIT 10
JOIN
clause
JOIN
clauseWhen join, put the small table on the left side, and each time you JOIN
or SELECT
, think about whether you can significantly reduce the amount of data.
IN
and NOT IN
clauses
IN
and NOT IN
clausesIN
and NOT IN
should also be used with caution, otherwise it will lead to a full table scan, for continuous values, BETWEEN
can be used, do not use IN
.
Apply for filters
Field filters
- First, type
{{variable_name}}
in the SQL statement:
SELECT * from `defi_protocol_daily_stats` [[where {{chain}}]]".
- Set the parameters in the right sidebar.
- Select the variable type as
field filter
and select the associated field.
- Finally, you can associate the filter field of the chart in the dashboard.
Text filters
- First, type
{{variable_name}}
in the SQL statement:
select * from defi_protocol_daily_stats [[where chain={{chain}}]]".
- Second, set the parameters in the right sidebar.
- Third, you must select the variable type as Text .
- Finally, you can type Ethereum in the filter input box and run it, and you can see that the result set has been filtered with Ethereum.
Tutorial: Parsing Ronin Chain Logs Using SQL
In this tutorial, we will walk through how to parse records from the ethereum_logs
table to analyze transactions related to the Ronin chain. This example demonstrates how to extract specific data from log entries using SQL.
Prerequisites
- Basic understanding of SQL.
- Familiarity with blockchain concepts, especially Ethereum logs.
- Access to a database containing the
ethereum_logs
table.
Step-by-Step Guide
1. Extract Date
We start by extracting the date from the block_timestamp
column. The date_trunc
function is used to truncate the timestamp to the day level.
select date_trunc('day', block_timestamp) as block_date, -- intercept date
2. Extract User Address
Next, we extract the user address from the log data. The address is located in the fourth part of the data
field. Each part is 64 characters long, and we start extracting from the third character.
substring(data, 3 + 64 * 3, 64) as hex_address, -- extract the 4th part of data into a user address, starting with the 3rd character, every 64 bits as a group
To format the address properly, we concatenate '0x' with the last 40 characters of the extracted data.
concat('0x', substring(substring(data, 3 + 64 * 3, 64), -40)) as address, -- extract the 4th part of the data into a user address, starting from the 3rd character, every 64 bits as a group, intercept 40 bits from the right, use '0x' to concatenate the intercepted data
3. Extract Token Address
Similarly, we extract the token address from the fifth part of the data
field.
concat('0x', substring(substring(data, 3 + 64 * 4, 64), -40)) as token, -- extract the 5th part of data into user address
4. Extract Transaction Amount
The amount is located in the twelfth part of the data
field. We extract it as a hexadecimal string first:
substring(data, 3 + 64 * 11, 64) as hex_amount, -- extracts the 12th part of the data
Then, we convert this hexadecimal string to a decimal value:
from_base(substring(data, 3 + 64 * 11, 64), 16) as amount, -- extracts the 12th part of the data and converts it to a decimal value
5. Include Transaction Hash
We also include the transaction_hash
in our results for reference:
transaction_hash
6. Filtering the Logs
To filter the logs, we use the following conditions:
- The
address
matches the Ronin Bridge V2 contract address. - The first topic matches the
Send
event signature. - The fourth part of the
data
field matches the WETH token address. - The
block_timestamp
is within the last 7 days.
from ethereum_logs
where address = '0x64192819ac13ef72bf6b5ae239ac672b43a9af08' -- Axie Infinity: Ronin Bridge V2
and element_at(topics, 1) = lower('0xd7b25068d9dc8d00765254cfb7f5070f98d263c8d68931d937c7362fa738048b') -- Send
and substring(data, 3 + 64 * 4, 64) = '00000000000000000000000000c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH, direct determination of hexadecimal value
and block_timestamp >= now() - interval '7' day
Full SQL Query
Here is the complete SQL query combining all the steps above:
select date_trunc('day', block_timestamp) as block_date, -- intercept date
substring(data, 3 + 64 * 3, 64) as hex_address, -- extract the 4th part of data into a user address, starting with the 3rd character, every 64 bits as a group
concat('0x', substring(substring(data, 3 + 64 * 3, 64), -40)) as address, -- extract the 4th part of the data into a user address, starting from the 3rd character, every 64 bits as a group, intercept 40 bits from the right, use '0x' to concatenate the intercepted data
concat('0x', substring(substring(data, 3 + 64 * 4, 64), -40)) as token, -- extract the 5th part of data into user address
substring(data, 3 + 64 * 11, 64) as hex_amount, -- extracts the 12th part of the data
from_base(substring(data, 3 + 64 * 11, 64), 16) as amount, -- extracts the 12th part of the data and converts it to a decimal value
transaction_hash
from ethereum_logs
where address = '0x64192819ac13ef72bf6b5ae239ac672b43a9af08' -- Axie Infinity: Ronin Bridge V2
and element_at(topics, 1) = lower('0xd7b25068d9dc8d00765254cfb7f5070f98d263c8d68931d937c7362fa738048b') -- Send
and substring(data, 3 + 64 * 4, 64) = '00000000000000000000000000c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH, direct determination of hexadecimal value
and block_timestamp >= now() - interval '7' day
Conclusion
This tutorial demonstrated how to parse Ethereum log data to extract specific information related to the Ronin chain. By following these steps, you can analyze transactions and gain insights into blockchain activities.
Updated 4 months ago