- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-17-2022 05:17 PM
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.
- Labels:
-
Ctas
-
Data
-
Data Engineering
-
External Tables
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2022 12:15 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2022 12:53 PM
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-18-2022 04:11 PM
Glad I could help