Data Engineering - CTAS - External Tables - Limitations of CTAS for external tables - can or cannot use options and location

Kopal
New Contributor II

Data Engineering - CTAS - External Tables

  1. Can someone help me understand why In chapter 3.3, we cannot not directly use CTAS with OPTIONS and LOCATION to specify delimiter and location of CSV?
  2. Or I misunderstood?

Details:

CREATE TABLE IF NOT EXISTS sales_csv
  (order_id LONG, email STRING, transactions_timestamp LONG, total_item_quantity INTEGER, purchase_revenue_in_usd DOUBLE, unique_items INTEGER, items STRING)
USING CSV
OPTIONS (
  header = "true",
  delimiter = "|"
)
LOCATION "${DA.paths.sales_csv}"
  • Video DE 3.3 - Set up Delta Tables
    • It is said that CTAS has limitations to support additional file OPTIONS. The example is to show how we could not define a pipe delimiter to correctly ingest data.
    • But this example opposes what was told in Chapter 2.2.
CREATE OR REPLACE TABLE sales_unparsed AS
SELECT * FROM csv.`${da.paths.datasets}/ecommerce/raw/sales-csv`;

Please help me understand.