Hi there,

I'm trying to understand if there's an easy way to create VIEWS and TABLES (I'm interested in both) *WITH* a provided schema file. For example, I understand that via dataframes I can accomplish this via something like this:

df = spark.read.schema(_schema).json(f'{dbfs_path}/{entity}.*json', multiLine=True).select('*','_metadata')

HOWEVER, I am struggling to find a way to do this exclusively via Spark SQL without needing to create a spark dataframe first.

Currently, I only see two approaches via Spark SQL: read_files and "using_json"

select *,_metadata
from read_files(
  'dbfs:/mnt/.../entity.*json'
  ,format => 'json'
  ,schema => '...'
  ,schemaHints => '...'
  ,rescuedDataColumn => '_rescued_data'
  )
;

CREATE OR REPLACE TEMPORARY VIEW entity_raw
USING json
OPTIONS (path='dbfs:/mnt/.../entity.*json', multiline=true)
;

Out of these options, "USING json" can clearly be used to create temporary views (and I presume permanent views as well), but it doesn't seem to support providing *CUSTOM* schema definitions (e.g. I have a filepath with ./schema/entity.json with explicit schema definitions).

On the other hand, the "read_files" option does support "manually entered" schema definitions... however, I don't see a way to provide an easy schema file (e.g. ./schema/entity.json) to fully define the schema. In a sense, with this option I want to avoid manually entering long and complex schema definitions.

Can someone help me understand if (A) I'm missing something with either of these options to provide a schemaLocation rather than needing to manually write out the schemas, or (B) if there's an "easy" way to convert a schema file (entity.json) into the proper format to include it with the read_files option.

Thanks in advance!