Databricks Data Engineer Associate Question 96
Select 2You are working on a Databricks notebook and need to reference a CSV file located in a mounted storage location (/mnt/data/sales.csv). You want to create a reusable reference to this file to simplify your queries. Which of the following approaches correctly reference the file while meeting your use case?
- A
Create a view using
CREATE OR REPLACE VIEW sales_view AS SELECT * FROM csv./mnt/data/sales.csv``. - B
Create a temporary view using
spark.read.csv('/mnt/data/sales.csv').createOrReplaceTempView('sales_temp_view'). - C
Use a Common Table Expression (CTE) with
WITH sales_cte AS (SELECT * FROM csv./mnt/data/sales.csv``) SELECT * FROM sales_cte`. - D
Create a temporary view using
CREATE OR REPLACE GLOBAL TEMP VIEW sales_global_view AS SELECT * FROM csv./mnt/data/sales.csv``. - E
Create a temporary view using Spark SQL with the command
CREATE TEMP VIEW sales_temp_view AS SELECT * FROM csv./mnt/data/sales.csv``.
Show answer and explanation
Correct answers: B, C
Explanation
To create a reference to a file in Databricks, you can use a temporary view (createOrReplaceTempView) or a Common Table Expression (CTE). Temporary views allow you to reference files and are tied to the Spark session. CTEs allow you to define a temporary query alias for use within a single query. Both approaches are valid and useful in different scenarios. The other options are invalid due to syntax errors or unsupported use cases in Databricks.
- A. Incorrect.
This option is incorrect because the syntax for referencing a file in a
CREATE VIEWstatement is invalid. Views cannot directly reference files in this manner. - B. Correct.
This option is correct because it uses
spark.read.csv()to load the CSV file into a DataFrame and creates a temporary view usingcreateOrReplaceTempView. This approach is valid and commonly used in Databricks. - C. Correct.
This option is correct because a Common Table Expression (CTE) can be used to reference the CSV file temporarily within a query. However, this reference is valid only for the duration of the query.
- D. Incorrect.
This option is incorrect because the
CREATE OR REPLACE GLOBAL TEMP VIEWsyntax is not valid for directly referencing a file. This syntax requires an underlying DataFrame or table. - E. Incorrect.
This option is incorrect because the
CREATE TEMP VIEWsyntax in Spark SQL cannot directly reference a file. It requires a DataFrame or a table as its source.