E-MapReduce:Query data in a Delta table

Limits

This topic applies to EMR V3.37.0 and later minor versions, EMR V5.3.0 and later minor versions, and EMR V4.10.0.

Use Spark to create a Delta table and query data in the table

1. Open the Spark CLI.
   1. Log on to the master node of your EMR cluster in SSH mode. For more information, see Log on to a cluster.
   2. Run the following command to open the Spark CLI:

      spark-sql

2. Create a table in Spark and query data in the table.
   1. Run the following command to create a Delta Lake table named delta_table in Spark:

      create table delta_table (id int) using delta location "/tmp/delta_table";

   2. Run the following command to insert data into the table:

      insert into delta_table values 0,1,2,3,4;

   3. Run the following command to query data in the table:

      select * from delta_table;

      The information that contains the following content is returned:

      2
      3
      4
      0
      1
      Time taken: 1.847 seconds, Fetched 5 row(s)

3. Query data in the Delta table.

   Use Hive to query data in the Delta table

   Important

   • In EMR V3.37.0 and later minor versions, if you want to use Hive to query data in a Delta table, you must add a custom parameter in the EMR console or by running commands. The parameter that you must add in the EMR console varies based on the execution engine of Hive. If you use Hive on MapReduce, you must add the hive.input.format parameter on the hive-site.xml subtab of the Configure tab for the Hive service and set the parameter to io.delta.hive.HiveInputFormat. If you use Hive on Tez, you must add the hive.tez.input.format parameter and set the parameter to io.delta.hive.HiveInputFormat. For more information, see Add parameters.

   • If you want to execute the ADD COLUMN statement to add a column to a Delta table, we recommend that you add the new field before the partition field. This prevents data exceptions from occurring when you use Hive to query data in the Delta table. For more information, see ALTER TABLE.

   1. Run the following command to open the Hive CLI:

      hive

   2. Run the following command to query data in the Delta table:

      select * from delta_table;

      The following output is returned:

      2
      3
      4
      0
      1
      Time taken: 2.937 seconds, Fetched: 5 row(s)

      Note

      The data that is returned is the same as the data that you inserted into the Delta table by using Spark. This indicates that the access to the Delta table from Hive is successful.

   Use Presto to query data in the Delta table

   1. Run the following command to open the Presto CLI:

      presto --server master-1-1:8889 --catalog delta --schema default --user hadoop

      Parameters in the preceding command:

      • --server master-1-1:8889: the IP address and port number of the Presto service.

      • --catalog delta: the name of the data source to which you want to connect.

      • --schema default: the name of the database that you want to use. In this example, a default database is used. You can modify this parameter based on your business requirements.

      • --user hadoop: the username for authentication.

   2. Run the following command to query data in the Delta table:

      select * from delta_table;

      The following output is returned:

       id
      ----
        2
        3
        4
        0
        1
      (5 rows)

      Note

      The data that is returned is the same as the data that you inserted into the Delta table by using Spark. This indicates that the access to the Delta table from Presto is successful.

   Use Trino to query data in the Delta table

   1. Run the following command to open the Trino CLI:

      trino --server master-1-1:9090 --catalog delta --schema default --user hadoop

      Parameters in the preceding command:

      • --server master-1-1:9090: the IP address and port number of the Trino service.

      • --catalog delta: the name of the data source to which you want to connect.

      • --schema default: the name of the database that you want to use. In this example, a default database is used. You can modify this parameter based on your business requirements.

      • --user hadoop: the username for authentication.

   2. Run the following command to query data in the Delta table:

      select * from delta_table;

      The following output is returned:

       id
      ----
        2
        3
        4
        0
        1
      (5 rows)

      Note

      The data that is returned is the same as the data that you inserted into the Delta table by using Spark. This indicates that the access to the Delta table from Trino is successful.

Create an external table in Hive

CREATE EXTERNAL TABLE delta_tbl (name string, age int, city string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
WITH SERDEPROPERTIES("path" = "/path/to/delta")
STORED AS INPUTFORMAT 'io.delta.hive.DeltaInputFormat'
OUTPUTFORMAT 'io.delta.hive.DeltaOutputFormat'
LOCATION '/path/to/delta'
TBLPROPERTIES("spark.sql.sources.provider" = "delta");

CREATE EXTERNAL TABLE delta_tbl123 (name string, age int, city string)
STORED BY 'io.delta.hive.DeltaStorageHandler'
WITH SERDEPROPERTIES("path" = "/path/to/delta")
LOCATION '/path/to/delta'
TBLPROPERTIES("spark.sql.sources.provider" = "delta");