Databricks Data Engineer Associate Question 253
Single answerYou are tasked with creating a managed table in Databricks to store customer sales data. The data is currently stored in a CSV file located in a mounted Azure Data Lake Storage. Which of the following commands will correctly create a managed table and load the data into it?
- A
CREATE TABLE sales_data USING CSV OPTIONS (path '/mnt/sales_data.csv')
- B
CREATE TABLE sales_data AS SELECT * FROM csv.
/mnt/sales_data.csv - C
CREATE OR REPLACE TABLE sales_data USING PARQUET OPTIONS (path '/mnt/sales_data.csv')
- D
CREATE TABLE sales_data (id INT, amount DOUBLE) USING CSV OPTIONS (path '/mnt/sales_data.csv')
Show answer and explanation
Correct answer: B
Explanation
To create a managed table in Databricks, you need to use the CREATE TABLE AS SELECT (CTAS) syntax to copy data into Databricks' storage. Managed tables are fully controlled by Databricks, and the data is stored in the Databricks file system (DBFS). The second option is correct because it creates a managed table and loads the data into it from the specified CSV file.
- A. Incorrect.
This command specifies the
USING CSVsyntax and provides a path, but it does not load the data into a managed table. Instead, it points to an external table, which is not the requirement. - B. Correct.
This command uses the
CREATE TABLE AS SELECTsyntax, which correctly creates a managed table by copying the data from the CSV file into Databricks' storage. - C. Incorrect.
This command uses the
CREATE OR REPLACE TABLEsyntax, but it specifies the PARQUET format for a CSV file, which is incorrect. Additionally, it points to an external file rather than creating a managed table. - D. Incorrect.
This command tries to define a schema for the table but still points to an external file and does not load the data into a managed table.