Comment on page
How to Configure a Mirror Node and Query Data
A step-by-step guide on how to configure a mirror node and query specific data on the Hedera Network
Hedera Mirror Node is a useful tool that lets developers and users access past transaction data on the Hedera network. You can view and analyze network data such as transactions, transfers, balances, and events from the past in a reliable, scalable, and efficient way. The best feature is the capability to configure your mirror node to query only data that meet your tailored specifications.
This guide provides step-by-step instructions on how to configure and use a Hedera Mirror Node to access past transaction data on the Hedera network. You will learn how to configure your mirror node to store only the latest 90 days of data or data for a specific entity (account, smart contract, etc.) and how to use basic SQL queries to analyze the data.
- Basic understanding of Hedera Mirror Nodes.
- Basic understanding of terminal commands and SQL.
- Java (openjdk@17: Java version 17), Gradle (the latest version), and PostgreSQL (the latest version) are installed on your machine.
- Docker (
>= v20.10.x)installed and open on your machine. Rundocker -vin your terminal to check the version you have installed.
Clone the Hedera mirror node repository and navigate to the project directory:
git clone https://github.com/hashgraph/hedera-mirror-node.git
cd hedera-mirror-node
Note: Cloning the mirror node repository could require some time to complete.
In this example, we will configure the mirror node to store the last 90 days of data or data for a specific account ID (entity). To achieve this, we will need to create and modify an
application.yml file. This file contains configuration settings for the mirror node, such as which data to store and how long to store it.First, create a new configuration folder and file inside the
hedera-mirror-importer directory. The Mirror Node importer directory contains the source code for the importer tool, which allows users to import data from the Hedera Mainnet, Testnet, or Previewnet. This creates and imports a read-only instance of the Hedera network data stored in its own database.Run the following command to create the right folder and file:
mkdir hedera-mirror-importer/config
touch hedera-mirror-importer/config/application.yml
The mirror node may be configured only to store a subset of data for entities and/or transaction types of interest — essentially, which rows of data to retain.
In this example, we'll use the
application.yml format for demonstration purposes. This configuration retains transaction and crypto transfer data for 90 days, excludes data for entity 0.0.111478, and includes specific transactions for entities 0.0.111710 and 0.0.111734. Furthermore, it prevents the storage of topic data. You can check out the other two alternative formats here if you don't like working with the YAML format.Here's an overview the
application.yml file. Copy the following lines into application.yml and save it.application.yml
hedera:
mirror:
importer:
network: DEMO
retention:
period: 90D
frequency: 60S
enabled: true
include:
- transaction
- crypto_transfer
parser:
exclude:
- entity: [0.0.111478]
include:
- transaction: [CRYPTOTRANSFER, CRYPTOCREATEACCOUNT]
- entity: [0.0.111710]
transaction: [CONTRACTCREATEINSTANCE]
- entity: [0.0.111734]
record:
entity:
persist:
topics: false
Here's a breakdown of what each section of the configuration file does:
behaviorhedera: This is the root section of the configuration file, indicating that the settings apply to the Hedera network.mirror: This is a sub-section that pertains specifically to the Mirror node.importer: This sub-section defines settings for the Mirror node's importer, which is responsible for retrieving transaction data from the network and storing it in a local database for querying.importer.network: DEMO: This specifies that the importer should connect to a bucket with demo data. It's the easiest way to experiment with the mirror node and importer. If you want to connect to theTESTNET,MAINNET, orPREVIEWNET, you need to follow this tutorial.importer.retention: This sub-section specifies the retention period and frequency for importing data. In this case, the importer will clean data that is older than 90 days every 60 seconds. If you omit thefrequencykey, the default behavior for cleaning data is once a day.importer.retention.include: This specifies the database tables that should be included in the imported data. The tables specified aretransactionandcrypto_transfer. You can find all tables in the GitHub repository for the mirror node.parser: This sub-section defines settings for the data parser, which determines the data that gets stored in the database or the data that should be filtered.parser.exclude: This specifies the entities or transaction types that should be excluded from the imported data. In this case, theparser.exclude.entitywith ID0.0.111478is excluded.parser.include: This specifies the entities or transaction types that should be included from the imported data. In this case, theparser.include.entitywith ID0.0.111478is included, and two specific transaction types (CRYPTOTRANSFERandCRYPTOCREATEACCOUNT) are included viaparser.include.transaction. You can also combineentityandtransactionfields. In our example, we only want to storeCONTRACTCREATEINSTANCEtransactions for the entity with ID0.0.111710.parser.record: This sub-section specifies how the imported data should be recorded. In this case, theentityobject is specified, which means that data should be recorded for each unique entity (account) involved in the transactions. Thepersistsetting is set tofalse, which means that topic data for entities should not be persisted.period: 90D: This indicates that the importer should retain the imported data for a period of 90 days. After this period, the data will be deleted.
Note: The
parser.exclude properties take priority over the parser.include properties. If you list the same value in both lists, it will be excluded.
In addition, the various boolean hedera.mirror.importer.record.entity.persist properties may be specified to control which additional fields get stored (which additional tables get recorded).See the
hedera.mirror.importer.parser.include.* and hedera.mirror.importer.parser.exclude.* properties listed in this table.application.properties
To configure the above scenario via
application.properties file, include the following lines:hedera.mirror.importer.network=DEMO
hedera.mirror.importer.retention.period=90D
hedera.mirror.importer.retention.frequency=60S
hedera.mirror.importer.retention.enabled=true
hedera.mirror.importer.retention.include=transaction,crypto_transfer
hedera.mirror.importer.parser.exclude.entity=0.0.111478
hedera.mirror.importer.parser.include.transaction=CRYPTOTRANSFER,CRYPTOCREATEACCOUNT
hedera.mirror.importer.parser.include.entity.0.0.111710.transaction=CONTRACTCREATEINSTANCE
hedera.mirror.importer.parser.include.entity=0.0.111734
hedera.mirror.importer.record.entity.persist.topics=false
environment variables
To configure the above scenario via environmental variables, set the following:
HEDERA_MIRROR_IMPORTER_NETWORK=DEMO
HEDERA_MIRROR_IMPORTER_RETENTION_PERIOD=90D
HEDERA_MIRROR_IMPORTER_RETENTION_FREQUENCY=60S
HEDERA_MIRROR_IMPORTER_RETENTION_ENABLED=true
HEDERA_MIRROR_IMPORTER_RETENTION_INCLUDE=transaction,crypto_transfer
HEDERA_MIRROR_IMPORTER_PARSER_EXCLUDE_ENTITY=0.0.111478
HEDERA_MIRROR_IMPORTER_PARSER_INCLUDE_TRANSACTION=CRYPTOTRANSFER,CRYPTOCREATEACCOUNT
HEDERA_MIRROR_IMPORTER_PARSER_INCLUDE_ENTITY_0_0_111710_TRANSACTION=CONTRACTCREATEINSTANCE
HEDERA_MIRROR_IMPORTER_PARSER_INCLUDE_ENTITY=0_0_111734
HEDERA_MIRROR_IMPORTER_RECORD_ENTITY_PERSIST_TOPICS=false
The PostgreSQL container is responsible for creating the database for the mirror node instance, and the REST API container allows you to use the REST APIs to query the mirror node instance. The database stores the transaction data retrieved by the importer component of the mirror node, and the REST API provides an interface for accessing that data using HTTP requests. The importer component is responsible for retrieving the transaction data from the Hedera network and storing it in the database. Let's start up the database!
Open Docker and start the PostgreSQL and REST API containers in the root directory:
docker compose up -d db rest && docker logs hedera-mirror-node-db-1 --follow
Wait until you see the "database system is ready to accept connections" message in the console log, then
control + c to terminate the current process.
Console output after starting both containers.
Now the database is ready, let's import demo data. Run the importer in the same root directory:
./gradlew :importer:bootrun
This process may take some time, but once you see this in your console and the process is at 92%, you kill the process with
control + c. If you let the process run, it will import more data that you don't need for this tutorial.
Mirror Importer process console
Note: Should you encounter an error during this step or if the command doesn't execute successfully, it's recommended to run the command again.
To connect the PostgreSQL database, we need to retrieve the database credentials. Open a new terminal window and run the following command in the root directory. Copy the database password that your console returns. You need this for the next step.
cat docker-compose.yml | grep OWNER
In the same directory, run the following command that will connect the database:
psql -U mirror_node -h 127.0.0.1
Enter the database password when prompted. If you successfully connect to the PostgreSQL database, your console should be ready to execute queries and look something like this:
hedera-mirror-node % psql -U mirror_node -h 127.0.0.1
Password for user mirror_node:
psql (14.6 (Homebrew), server 14.7)
Type "help" for help.
mirror_node=> <ENTER YOUR SQL QUERIES GO HERE>
In this section, you can try out multiple queries that show you how to retrieve data from the PostgreSQL database. You need a basic understanding of SQL queries to craft your own queries.
Most queries include the field
type which refers to a transaction type, e.g. 11 refers toCRYPTOCREATEACCOUNTand 14 refers toCRYPTOTRANSFER. The most common transaction types are:- Type 7:
CONTRACTCALL - Type 11:
CRYPTOCREATEACCOUNT - Type 14:
CRYPTOTRANSFER - Type 24:
CONSENSUSCREATETOPIC - Type 27:
CONSENSUSSUBMITMESSAGE - Type 29:
TOKENCREATION - Type 37:
TOKENMINT - Type 40:
TOKENASSOCIATE
Execute the following queries to analyze the data stored in your local database.
-- 1. query counts transactions based on their type and filters out
-- types 11 (CRYPTOCREATEACCOUNT) and 14 (CRYPTOTRANSFER)
SELECT count(*), payer_account_id, entity_id, type
FROM transaction WHERE type not in (11, 14)
GROUP by payer_account_id, entity_id, type order by type;
-- 2. query counts transactions based on their payer account ID or entity ID
-- and filters for the value 111478
SELECT count(*), payer_account_id, entity_id, type
FROM transaction WHERE payer_account_id=111478 OR entity_id=111478
GROUP by payer_account_id, entity_id, type ORDER by type;
-- 3. query only type 8 -> CONTRACTCREATEINSTANCE (CRYPTOCREATEACCOUNT, CRYPTOTRANSFER)
SELECT * FROM transaction WHERE type=8;
-- 4. query count for each transaction type
SELECT count(*), type
FROM transaction
GROUP by type;
-- 5. query shows different fields for the transaction table
SELECT payer_account_id, node_account_id, result, type, charged_tx_fee, transaction_hash
FROM transaction
WHERE type not in (11, 14);
Note that your output might differ depending on how much data you have imported into your database with the
./gradlew :importer:bootrun command.-- Query 1:
count | payer_account_id | entity_id | type
-------+------------------+-----------+------
5 | 1117 | 61457 | 7
71 | 111699 | 111704 | 7
2 | 111721 | 111728 | 7
2 | 111749 | 111757 | 7
2 | 111478 | 111482 | 7
1 | 111749 | 111757 | 8
(...)
-- Query 2:
count | payer_account_id | entity_id | type
-------+------------------+-----------+------
2 | 111478 | 111482 | 7
1 | 111478 | 111482 | 8
1 | 111478 | 111482 | 9
1 | 111478 | 111595 | 11
1 | 111478 | 111647 | 11
1 | 111478 | 111644 | 11
(...)
-- Query 3:
consensus_timestamp | type | result | payer_account_id | valid_start_ns | valid_duration_seconds | node_account_id | entity_id | initial_balance | max_fee | charged_tx_fee | memo | transaction_hash | transaction_bytes | scheduled | nonce | parent_consensus_timestamp | errata | index
---------------------+------+--------+------------------+---------------------+------------------------+-----------------+-----------+-----------------+------------+----------------+--------------------------------+----------------------------------------------------------------------------------------------------+-------------------+-----------+-------+----------------------------+--------+-------
1570800945787190002 | 8 | 22 | 111152 | 1570800933439239200 | 30 | 3 | 111157 | 0 | 5000000000 | 3581540281 | \x637265617465436f6e7472616374 | \xd226f8dd7922f9024331c5bf00da8e5bae6ade0db24fa4283e925ef9f06b4b4cdbbf1ff6511e552fe1a669a9b9cbaca8 | | f | 0 | | | 8
1570801009263126000 | 8 | 22 | 111158 | 1570800996904028800 | 30 | 3 | 111164 | 0 | 9000000000 | 1991432867 | \x637265617465436f6e7472616374 | \xcb04329381668a481a0734cabfeac28b56f74cd9e3db42af031a47a6c98a598555d6d502623d237c679e00ee9b61dfc2 | | f | 0 | | | 14
(...)
-- Query 4:
count | type
-------+------
30 | 12
6676 | 14
34 | 17
13 | 8
572 | 11
10 | 9
82 | 7
146 | 19
7 | 18
4 | 16
894 | 15
(...)
-- Query 5:
payer_account_id | node_account_id | result | type | charged_tx_fee | transaction_hash
------------------+-----------------+--------+------+----------------+----------------------------------------------------------------------------------------------------
111146 | 3 | 22 | 17 | 608622391 | \x9a8f13e9989462a2028bc5e3946d0f3d556d4ccfc65f2afa55b94c535823ea8a27caf630de85ae22303a73b7a0ded98d
111146 | 3 | 22 | 17 | 653540869 | \xd4964bfe66d311c60717c733e39c1e4b4aae38138e0f583fef9ae3844eb95772862f5f467ebb96efd06e80ba0b0059da
111146 | 3 | 22 | 15 | 1256006 | \x1c2b27dbf2d093fa353fc444e0a3ed1ac932518a37d2b03509f8a06039d454df82aeba5f35d7a1fea5babaca298a4288
(...)
The PostgreSQL database is not an end product; the Hedera Mirror Node REST API is. Hence, SQL queries might fail when the engineering team updates the underlying database model in new releases. This tutorial has been tested with release v0.79.1.
To exit the
psql console, run the quit command:\q
Lastly, run the following command to stop and remove the created containers:
docker compose down
Congratulations! 🎉 You have successfully learned how to configure the Hedera Mirror Node to query specific data. Feel free to reach out on Discord if you have any questions!