204: Data Ingestion

Ingest data into IBM watsonx.data

Before running the utility, you need to retrieve several credentials for MinIO and the keystore
password.

1. While still in the /root/ibm-lh-dev/bin directory, enter following commands to extract the
   credentials. You need to generate three export lines and create a directory that will be used
   later in another script.

export LH_S3_ACCESS_KEY=$(docker exec ibm-lh-presto \
printenv | grep LH_S3_ACCESS_KEY | sed 's/.*=//')
bash
Copy code to clipboard

export LH_S3_SECRET_KEY=$(docker exec ibm-lh-presto \
printenv | grep LH_S3_SECRET_KEY | sed 's/.*=//')
bash
Copy code to clipboard

export LH_KEYSTORE_PASSWORD=$(docker exec ibm-lh-presto \
printenv | grep LH_KEYSTORE_PASSWORD | sed 's/.*=//')
bash
Copy code to clipboard

2. Run the following commands to create the staging directory. The staging directory is used to
   move files between this system and the docker container where the ibm-lh command-line
   utility is running.

export staging=/root/ibm-lh-client/localstorage/volumes/infra/staging
bash
Copy code to clipboard

mkdir -p ${staging}
cat <<EOF > ${staging}/keys.sh
#!/bin/bash
export access_key=$LH_S3_ACCESS_KEY
export secret_key=$LH_S3_SECRET_KEY
export keystore_password=$LH_KEYSTORE_PASSWORD
EOF
chmod +x ${staging}/keys.sh
bash
Copy code to clipboard

3. Run the following commands to copy the MinIO SSL certificate from the docker container to
   staging directory. The MinIO SSL certificate is needed for copying operation to take place. In
   addition, the file that you want loaded into watsonx.data will be moved into the staging file
   directory.

docker cp ibm-lh-presto:/mnt/infra/tls/lh-ssl-ts.jks \
${staging}/lh-ssl-ts.jks
cp -f /sampledata/yellow_tripdata_2022-01.parquet \
${staging}/
bash
Copy code to clipboard

Create a hive schema for staging the ingest file

Before ingesting the file, you need to create a new schema for the table you are creating and
loading. You will create the schema using the watsonx.data user interface. Your TechZone
reservation email will include the URL of your watsonx.data user interface.

1. Log in to the IBM watsonx.data console:
   The URL to the watsonx.data console can be found in your TechZone reservation email
   details (see the Watsonx UI line in the Published services section).

   • Enter ibmlhadmin in the Username field.

   • Enter password in the Password field.

   • Click the Log in button.

   

2. Click the Data manager icon from the left-side menu.
   

   

3. Click the Create dropdown menu and select Create schema.
   

   

4. In the Create schema dialog, perform the following steps:

   • Click the Catalog dropdown menu and select hive_data.

   • Enter staging in the Name field.

   • Click the blue Create button.
   

   

You should see the new staging schema under hive_data catalog in the Catalogs associated
section.

5. Repeat step 3 to create another schema. In the Create schema dialog, perform the following
   steps:

   • Click the Catalog dropdown menu and select iceberg_data.

   • Enter ingest in the Name field.

   • Click the blue Create button.
   

   

Ingest using ibm-lh command-line utility

You will use the ibm-lh command line utility to ingest data. You will leave the watsonx.data UI
for now to do the next steps in the command line window.

1. To access the ibm-lh command line utility, enter the following command:

./ibm-lh data-copy
bash
Copy code to clipboard

2. Once inside the utility, run the following command to get more details on the data-copy
   option.

ibm-lh data-copy --help
bash
Copy code to clipboard

3. Run the following command to exit the tools container.

exit
bash
Copy code to clipboard

4. Run the following script to ingest the taxi data (yellow_tripdata_2022_01.parquet) into the
   yellow_tripdata_2022_01_localfile table in watsonx.data.

cat <<EOF > ${staging}/ingest-local.sh
#!/bin/bash
dir=/mnt/infra/staging
cd \${dir}
source ./keys.sh
table_name="iceberg_data.ingest.yellow_tripdata_2022_01_localfile"
file="yellow_tripdata_2022-01.parquet"
export STAGING_S3_CREDS="AWS_SECRET_ACCESS_KEY=\${secret_key}\\
,AWS_ACCESS_KEY_ID=\${access_key}\\
,AWS_REGION=us-east-1\\
,BUCKET_NAME=iceberg-bucket\\
,ENDPOINT_URL=http://ibm-lh-minio:9000"
ibm-lh data-copy \\
 --source-data-files \${dir}/\${file} \\
 --target-tables \${table_name} \\
 --ingestion-engine-endpoint "hostname=ibm-lh-presto-svc,port=8443" \\
 --staging-location s3://iceberg-bucket/ingest/ \\
 --staging-hive-catalog hive_data \\
 --staging-hive-schema staging \\
 --create-if-not-exist \\
 --trust-store-path \${dir}/lh-ssl-ts.jks \\
 --trust-store-password \${keystore_password} \\
 --dbuser ibmlhadmin \\
 --dbpassword password
EOF
bash
Copy code to clipboard

sed -i '/^$/d' ${staging}/ingest-local.sh
bash
Copy code to clipboard

chmod +x ${staging}/ingest-local.sh
bash
Copy code to clipboard

5. Run the following command to start the ibm-lh command line utility again:

./ibm-lh data-copy 
bash
Copy code to clipboard

6. Now run the ingest job inside the tool container by executing the following command:

/mnt/infra/staging/ingest-local.sh
bash
Copy code to clipboard

7. After ingesting the data, exit the command line utility.

exit
bash
Copy code to clipboard

8. Go back to the IBM watsonx.data UI. From the Data manager section, Expand the
   iceberg_data catalog in the Catalogs associated area and then expand the ingest schema.
   

   

9. Select the yellow_tripdata_2021_01_localfile table to see the table definition. Select the
   Table schema tab to view the table schema if not already selected.
   

   

10. Select the Data sample tab to see a sample of the data.
    

    

11. Now you can use the UI to run a query against this imported data. Select the
    Query workspace (SQL) icon from the left side menu.
    

    

12. On the line where the yellow_tripdata_2022_01_localfile table is located, click the
    Query templates icon at the end of the name. This will display a drop-down list. Select
    Generate SELECT
    

    

This generates SQL SELECT statement in the window to the right of the table name.

13.In order to review the results, execute the query by clicking on the blue
Run on presto-01 button.

Summary

You have now completed the lab!
In this lab you ingested data by using the watsonx.data ibm-lh command-line utility and ran
a SQL statement to query the data that was ingested.

Congratulations, you've reached the end of lab 204.

If you would like to take the quiz covering the L4 hands on labs, it can be found here