Skip to content

Latest commit

 

History

History
114 lines (98 loc) · 3.78 KB

Lab6.md

File metadata and controls

114 lines (98 loc) · 3.78 KB
Raw

Lab 6: Import data into MDS and load tables to HeatWave

Key Objectives:

  • Learn how to connect to MDS and import a dataset

Steps

Step 6.1:

  • Back to the ssh connection.

  • Connect to MySQL DB System using MySQL Shell, with the following command:
mysqlsh --user=admin --password=Oracle.123 --host=<mysql_private_ip_address> --port=3306 --js

Step 6.2:

  • From the MySQL Shell connection, import the data set into MySQL DB System:
util.loadDump("/home/opc/tpch_dump", {dryRun: true, resetProgress:true, ignoreVersion:true})

This command will run a dry run of the import. If it terminates without errors, execute the followin to load the dump for real:

util.loadDump("/home/opc/tpch_dump", {dryRun: false, resetProgress:true, ignoreVersion:true})

Step 6.3:

  • Check the imported data. From MySQL Shell execute the commands:
\sql

SHOW DATABASES;

(You shoul see the following output:)

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tpch               |
+--------------------+

Continue with commands:

USE tpch;

SHOW TABLES;

(You shoul see the following output:)

+----------------+
| Tables_in_tpch |
+----------------+
| customer       |
| lineitem       |
| nation         |
| orders         |
| part           |
| partsupp       |
| region         |
| supplier       |
+----------------+

Step 6.4:

  • Let's start testing a simple query but yet effective query. From the previous SQL prompt, run the following query and check the execution time (approximately 12-13s):
SELECT
    l_returnflag,
    l_linestatus,
    SUM(l_quantity) AS sum_qty,
    SUM(l_extendedprice) AS sum_base_price,
    SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
    SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
    AVG(l_quantity) AS avg_qty,
    AVG(l_extendedprice) AS avg_price,
    AVG(l_discount) AS avg_disc,
    COUNT(*) AS count_order
FROM
    lineitem
WHERE
    l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY
GROUP BY l_returnflag , l_linestatus
ORDER BY l_returnflag , l_linestatus;

+--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
| l_returnflag | l_linestatus | sum_qty     | sum_base_price  | sum_disc_price    | sum_charge          | avg_qty   | avg_price    | avg_disc | count_order |
+--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
| A            | F            | 37734107.00 |  56586554400.73 |  53758257134.8700 |  55909065222.827692 | 25.522006 | 38273.129735 | 0.049985 |     1478493 |
| N            | F            |   991417.00 |   1487504710.38 |   1413082168.0541 |   1469649223.194375 | 25.516472 | 38284.467761 | 0.050093 |       38854 |
| N            | O            | 74476040.00 | 111701729697.74 | 106118230307.6056 | 110367043872.497010 | 25.502227 | 38249.117989 | 0.049997 |     2920374 |
| R            | F            | 37719753.00 |  56568041380.90 |  53741292684.6040 |  55889619119.831932 | 25.505794 | 38250.854626 | 0.050009 |     1478870 |
+--------------+--------------+-------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
4 rows in set (12.6570 sec)
  • Exit from MySQL Shell:
 \exit

Conclusion

As we can see the time consumed to run the query is relatively long, so let's enable Heatwave service and run the query again to compare the result in the next Lab!

<< Go to Lab 5 | Home | Go to Lab 7 >>