Use the following SQL SELECT INTO syntax to export query results to CSV files.

SELECT ... INTO OUTFILE 
{URL | URI} 'uri'
[FORMAT csv]
[CHARACTER SET 'charset_name']
[HEADER {ON | OFF}]
[{FIELDS | COLUMNS} 
  [NULL AS 'null_char']
  [TERMINATED BY 'string'] 
  [[OPTIONALLY] ENCLOSED BY 'char']
  [ESCAPED BY 'char'] ...
]
[LINES [TERMINATED BY 'string'] [STARTING BY 'string'] ...]

The following parameters are included in the SQL syntax to export the query results.

The following example uses default delimiters, and exports the query results to a CSV file. The file location is represented as an OCIFS URI. The URI keyword is supported as of MySQL 9.4.0.

SELECT sum(l_quantity) from lineitem 
INTO OUTFILE URI 
'oci://bucket1@tenancy1/data_files_1/';

The following example uses default delimiters, and exports the query results to a CSV file. The file location is represented as Resource principals.

SELECT sum(l_quantity) from lineitem 
INTO OUTFILE URL 
'https://objectstorage.region1.oraclecloud.com/n/tenancy1/b/usr-bucket/o/summary/2024/Mar/';

The following example uses default delimiters, and exports the query results to a CSV file with a header row. The file location is represented as a PAR URL.

SELECT sum(l_quantity) from lineitem 
INTO OUTFILE URL 
'https://objectstorage.region1.oraclecloud.com/p/PTn9XsVegDBbSz1v-FgDma4G_e_78fJjv1GK04zlSqD9975MPZydieUJAGUypxQbn/tenancy1/b/usr-bucket/o/", "prefix":summary/2024/Mar/'
FORMAT csv 
HEADER ON;

In the following example:

Therefore, instead of having queries compute results by scanning all the rows for a given range of dates, you can run queries against this aggregate table, saving both time and cost.

The following query computes the sum of quantity and price and groups it by year, month, and day for the year 1992. The tenancy, bucket and the folder to save the query results are provided in the URL parameter mentioned in the INTO OUTFILE clause.

SELECT SUM(l.l_quantity) 
  sum_quantity,
  SUM(o.o_totalprice) total_price,
  n.n_name nation_name,
  YEAR(o.o_orderdate) year,
  MONTH(o.o_orderdate) month,
  MONTHNAME(o.o_orderdate) month_name,
  DAY(o.o_orderdate) day_number
FROM 
  lineitem l, 
  orders o, 
  nation n,
  customer c
WHERE 
  l.l_orderkey = o.o_orderkey
  and n.n_nationkey = c.c_nationkey
  and c.c_custkey = o.o_custkey
  and YEAR(o.o_orderdate) = '1992'
GROUP BY 
  YEAR(o.o_orderdate),
  nation_name,
  MONTH(o.o_orderdate),
  MONTHNAME(o.o_orderdate),
  DAY(o.o_orderdate)
INTO OUTFILE
  URL 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/mytenancy/b/usr-bucket/o/summary-layer/' 
  FORMAT csv HEADER ON;  

After the query runs successfully, a destination folder with a unique system-generated prefix as the folder name is created, and the query results are stored in that folder. Refer to the sample success message.

 Note Code (3877) : Query results will be written to object storage with prefix : 'summary-layer/29597afd-ab15-11ef-83fa-02001702ecc6'

You can use Auto Parallel Load to load query results (CSV files) into a new aggregate table named lineitem_agg in MySQL HeatWave. Auto Parallel Load automatically gets the schema of the CSV files and creates the necessary table in the specified database and loads all the data from the CSV files into the new aggregate table.

 SET @input_list = '[
  {
    "db_name": "lakehouse",
    "tables": [
      "lineitem_agg",
      {
        "table_name": "lineitem_agg",
        "engine_attribute": 
          {
            "dialect": 
              {
                "format": "csv",  
                "field_delimiter": ",",  
                "has_header": true, 
                "record_delimiter": "\\n", 
                "is_strict_mode": false
              },
            "file": 
              [{
                "region": "us-ashburn-1", 
                "namespace": "mytenancy", 
                "bucket": "usr-bucket",
                "prefix": "summary-layer/"
              }]
          }
      }
    ]
  }]';

SET @options = JSON_OBJECT('mode', 'normal', 'refresh_external_tables', 'true');
CALL sys.heatwave_load(CAST(@input_list AS JSON), @options); 

After the aggregate table loads successfully, you can use it to do the following.