Question
You are building a database in an Azure Synapse Analytics serverless SQL pool.
You have data stored in Parquet files in an Azure Data Lake Storage Gen2 container.
Records are structured as shown in the following sample.
```json
{
"id":123,
"address_housenumber": "19c",
"address_line1": "Memory Lane",
"applicant1_name": "Jane",
"applicant2_name": "Dev"
}
```
The records contain two applicants at most.
You need to build a table that includes only the address fields.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
```sql
[Dropdown 1] applications
WITH (
LOCATION = 'applications/',
DATA_SOURCE = applications_ds,
FILE_FORMAT = applications_file_format
)
AS
SELECT id, [address_housenumber] as addressnumber, [address_line1] as addressline1
FROM
[Dropdown 2] (BULK 'https://contoso1.dfs.core.windows.net/applications/year=*/*.parquet',
FORMAT = 'PARQUET') AS [r]
GO
```
Explanation
Dropdown 1 is CREATE EXTERNAL TABLE: a statement with LOCATION, DATA_SOURCE and FILE_FORMAT followed by AS SELECT is CREATE EXTERNAL TABLE AS SELECT (CETAS), which stores the query results in storage and exposes them as a table. CREATE TABLE and CREATE VIEW do not take these options.
Dropdown 2 is OPENROWSET, because OPENROWSET with BULK and FORMAT = 'PARQUET' reads the Parquet files directly from storage. OPENJSON parses JSON text rather than reading files, and CROSS APPLY joins rowsets, so neither can take a BULK path.
