38 of 55. A data engineer is working with Spark SQL and has a large JSON file stored at /data/input.json. The file contains records with varying…

The goal is to create an external table from a JSON file with varying schemas. The CREATE EXTERNAL TABLE statement is used to create a table that points to an external data location. The USING json clause specifies the data source format. To handle records with different structures, the mergeSchema option must be set to 'true'. This option instructs Spark to infer the schema by scanning the JSON files and merging the different schemas into a single, unified schema that can accommodate all fields present in the dataset. This directly fulfills all requirements of the prompt.

B. This creates a managed table, not an external one as required. It also omits the mergeSchema option needed to handle varying schemas.

C. While schema inference is desired, inferSchema is not the correct option for merging different schemas. The mergeSchema option is specifically designed for this purpose with JSON and Parquet sources.

D. mergeAll is not a valid Spark SQL option for data sources. The correct and officially supported option is mergeSchema.

1. Apache Spark 3.4.1 Documentation

SQL Reference

Data Sources

JSON Files: The documentation lists available options for the JSON data source. It explicitly states: "mergeSchema: sets whether to merge schemas from multiple JSON files. If true

Spark will merge schemas from all JSON files. Otherwise

it will infer the schema from the first file." This confirms mergeSchema is the correct option.

Source: Apache Spark Documentation - JSON Files

2. Databricks SQL Reference

CREATE TABLE: This documentation outlines the syntax for creating tables. The EXTERNAL keyword is specified for creating external tables

and the OPTIONS clause is used to pass data source-specific configurations like path and mergeSchema.

Source: Databricks Documentation - CREATE TABLE

3. Databricks Documentation

Read and write JSON files: This guide explains how to work with JSON files and explicitly mentions schema handling: "To have Spark combine the schemas of all files

set mergeSchema to true." This directly supports the use of the mergeSchema option for the stated problem.

Source: Databricks Documentation - JSON files (See section on Schema).