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

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

Kopal — Sun, 18 Dec 2022 01:17:10 GMT

Data Engineering - CTAS - External Tables

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?
Or I misunderstood?

Details:

In Data Engineering with Databricks V3
Video DE 2.2 - providing options
- We create external table where we leverage - USING for delimiter and LOCATION

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.

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

Anonymous — Sun, 18 Dec 2022 20:15:00 GMT

The 2nd statement CTAS will not be able to parse the csv in any manner because it's just the from statement that points to a file. It's more of a traditional SQL statement with select and from. It will create a Delta Table. This just happens to be the way the syntax works with AS not allowing for options.

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

Kopal — Sun, 18 Dec 2022 20:53:32 GMT

Ahh the devil was in the details. The statements differ in USING CSV -> external table and CTAS + SELECT FROM-> managed table. I hope I got it right.

Thanks a lot for explanation. Cheers!

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

Anonymous — Mon, 19 Dec 2022 00:11:56 GMT

Glad I could help