Working with data
After deploying the basic Data Lakehouse architecture, the next step is to fill the service with data and analyze it. This instruction provides examples of loading data from three different sources into Data Lakehouse and running SQL queries to analyze the loaded data. All data manipulations are performed via SQL queries to external systems using Cloud Trino.
After completing all the steps of this instruction, you will learn:
- how to load data into Data Lakehouse from various external sources: PostgreSQL DB, MySQL DB, as well as from a virtual DB included in the basic Cloud Trino package;
- how to use Cloud Trino to run SQL queries for analyzing, processing, and transforming data loaded into Data Lakehouse;
- how to export data from Data Lakehouse to external DBs;
- how to use Cloud Trino to run hybrid analytical SQL queries against multiple data sources simultaneously, including Data Lakehouse and external DBs.
-
Prepare the external PostgreSQL and MySQL databases.
The following are used as an example:
- MySQL DB
mydb1containing atable1table with an arbitrary structure; - PostgreSQL DB
db1containing atable2table with an arbitrary structure.
- MySQL DB
-
Deploy the basic architecture of the Data Lakehouse service.
As an example, during deployment when configuring Cloud Trino, the following were created:
- connection
s3db1to the VK Object Storage storage; - connection
pgdb1to the PostgreSQL DBdb1; - connection
mysqldb1to the MySQL DBmydb1.
After Cloud Trino is deployed, two connections to virtual DBs are automatically added to the configuration for generating large volumes of synthetic data and testing: tpcds and tpch. The example uses the tpcds connection, and one of its available data schemas
sf10is used for queries to the virtual DB.For queries to the PostgreSQL DB, the example uses the standard
publicdata schema. - connection
-
Install any SQL client on your computer for sending queries to Cloud Trino. All further queries are executed in the SQL client.
-
Create the
sch1schema for loading data from the external MySQL and PostgreSQL DBs:CREATE SCHEMA s3db1.sch1; -
Create the
sch2data schema for storing computation results:CREATE SCHEMA s3db1.sch2; -
Specify the data schema that will be used for loading data from the virtual DB:
CREATE SCHEMA s3db1.sf10;
Expected result: the SQL client displays a message about the successful creation of new data schemas.
Information about each S3 storage schema is stored in the Cloud Iceberg Metastore service until the first table is created in that schema (via the CREATE TABLE command).
Once the first table is created, Cloud Iceberg Metastore will create a directory in the S3 storage and save the following information there:
- table data in PARQUET format;
- metadata in JSON format;
- additional files, statistics, etc.
-
Query data from the
table1table of the external MySQL DB and load it into a new tabletable1_from_mysqlin thesch1schema of the S3 storage:CREATE TABLE s3db1.sch1.table1_from_mysql ASSELECT * FROM mysqldb1.mydb1.table1; -
Query data from the
table2table of the external PostgreSQL DB and load it into a new tabletable2_from_pgin thesch1schema of the S3 storage:CREATE TABLE s3db1.sch1.table2_from_pg ASSELECT * FROM pgdb1.public.table2;
Expected result: the SQL client displays a message about the successful creation of new tables.
Query data from several virtual DB tables (customer, store, store_sales, store_returns) and load them into new tables with the same names in the sf10 schema of the S3 storage:
CREATE TABLE s3db1.sf10.customer ASSELECT * FROM tpcds.sf10.customerCREATE TABLE s3db1.sf10.store ASSELECT * FROM tpcds.sf10.storeCREATE TABLE s3db1.sf10.store_sales ASSELECT * FROM tpcds.sf10.store_salesCREATE TABLE s3db1.sf10.store_returns ASSELECT * FROM tpcds.sf10.store_returns
Expected result: the SQL client displays a message about the successful creation of new tables.
The purpose of the analysis is to identify customers with a suspiciously high level of returns (higher than their purchases), which may indicate fraud or product quality issues.
The data for analysis was loaded into the sf10 schema of the S3 storage in the previous step:
- the
customertable — customer data; - the
storetable — store data; - the
store_salesandstore_returnstables — sales and returns data.
To perform the analysis, run the SQL query:
CREATE TABLE s3db1.sch2.analysis_from_s3 ASWITH sales AS (SELECT c.c_customer_sk,c.c_first_name,c.c_last_name,c.c_email_address,SUM(ss.ss_coupon_amt) AS coupon,SUM(ss.ss_sales_price) AS salesprice,SUM(ss.ss_ext_discount_amt) AS discountFROM s3db1.sf10.customer cJOIN s3db1.sf10.store_sales ss ON ss.ss_customer_sk = c.c_customer_skJOIN s3db1.sf10.store s ON s.s_store_sk = ss.ss_store_skGROUP BY c.c_customer_sk,c.c_first_name,c.c_last_name,c.c_email_address),storereturns AS (SELECT c.c_customer_sk,SUM(sr.sr_return_amt) AS returnamout,SUM(sr.sr_fee) AS feeFROM s3db1.sf10.customer cJOIN s3db1.sf10.store_returns sr ON sr.sr_customer_sk = c.c_customer_skJOIN s3db1.sf10.store s ON s.s_store_sk = sr.sr_store_skGROUP BY c.c_customer_sk)SELECT ss.c_customer_sk,ss.c_first_name,ss.c_last_name,ss.c_email_address,ss.salesprice,ss.discount,sr.returnamout,sr.feeFROM sales ssJOIN storereturns sr ON ss.c_customer_sk = sr.c_customer_skWHERE ss.salesprice < sr.returnamoutORDER BY ss.discount DESC limit 10
Cloud Trino executes the SQL query in the following order:
- Creates a SQL query execution plan.
- Accesses the S3 storage tables in the
sf10schema, where the information from the virtual DB was previously written, via the Iceberg Metastore service API. - In accordance with the execution plan, reads and processes the data: applies the filters and grouping criteria specified in the query, computes aggregates, etc.
- As a result of data processing, forms a list of customers with related objects that satisfies the SQL query.
- Writes the resulting data sample as a new table
analysis_from_s3to thesch2schema of the S3 storage.
Expected result: the SQL client displays 10 records about customers with a suspiciously high level of returns who received the largest discounts.
-
Query data from the
customertable in thesf10schema of the S3 storage and save it as a new table with the same name in the external MySQL DB.CREATE TABLE mysqldb1.mydb1.customer ASSELECT * FROM s3db1.sf10.customer; -
Query data from the
storetable in thesf10schema of the S3 storage and save it as a new table with the same name in the external PostgreSQL DB.CREATE TABLE pgdb1.public.store ASSELECT * FROM s3db1.sf10.store;
Expected result: the SQL client displays a message about the successful creation of new tables.
The purpose of the analysis is to identify customers with a suspiciously high level of returns (higher than their purchases), which may indicate fraud or product quality issues.
The data for analysis is retrieved simultaneously from three sources:
- customer data — from the
customertable in the external MySQL DB; - store data — from the
storetable in the external PostgreSQL DB; - sales and returns data — from the
store_salesandstore_returnstables in thesf10schema of the S3 storage.
To perform the analysis, run the SQL query:
CREATE TABLE s3db1.sch2.analysis_hybrid ASWITH sales AS (SELECT c.c_customer_sk,c.c_first_name,c.c_last_name,c.c_email_address,SUM(ss.ss_coupon_amt) AS coupon,SUM(ss.ss_sales_price) AS salesprice,SUM(ss.ss_ext_discount_amt) AS discountFROM mysqldb1.mydb1.customer cJOIN s3db1.sf10.store_sales ss ON ss.ss_customer_sk = c.c_customer_skJOIN pgdb1.public.store s ON s.s_store_sk = ss.ss_store_skGROUP BY c.c_customer_sk,c.c_first_name,c.c_last_name,c.c_email_address),storereturns AS (SELECT c.c_customer_sk,SUM(sr.sr_return_amt) AS returnamout,SUM(sr.sr_fee) AS feeFROM mysqldb1.mydb1.customer cJOIN s3db1.sf10.store_returns sr ON sr.sr_customer_sk = c.c_customer_skJOIN pgdb1.public.store s ON s.s_store_sk = sr.sr_store_skGROUP BY c.c_customer_sk)SELECT ss.c_customer_sk,ss.c_first_name,ss.c_last_name,ss.c_email_address,ss.salesprice,ss.discount,sr.returnamout,sr.feeFROM sales ssJOIN storereturns sr ON ss.c_customer_sk = sr.c_customer_skWHERE ss.salesprice < sr.returnamoutORDER BY ss.discount DESC limit 10
Cloud Trino executes the SQL query in the following order:
- Creates a SQL query execution plan.
- Accesses the S3 storage tables in the
sf10schema, where the information from the virtual DB was previously written, via the Iceberg Metastore service API. - Sends requests directly to the external DBs to retrieve data from the
customertable of the MySQL DB and thestoretable of the PostgreSQL DB. - In accordance with the execution plan, reads and processes the data: applies the filters and grouping criteria specified in the query, computes aggregates, etc.
- As a result of data processing, forms a list of customers with related objects that satisfies the SQL query.
- Writes the resulting data sample as a new table
analysis_from_s3to thesch2schema of the S3 storage.
Expected result: the SQL client displays 10 records about customers with a suspiciously high level of returns who received the largest discounts.
The previously deployed Data Lakehouse service infrastructure consumes computing resources and is billed. If you no longer need it, delete it.