How to ingest files from volume using autoloader

pshuk — Thu, 21 Dec 2023 15:40:54 GMT

I am doing a test run. I am uploading files to a volume and then using autoloader to ingesgt files and creating a table. I am getting this error message:

-----------------------------------------------------------

com.databricks.sql.cloudfiles.errors.CloudFilesIllegalStateException: The container in the file event `{"backfill":{"bucket":"root@dbstoragepdarecwn6h6go","key":"7019658555662308/FileStore/LiveDataUpload/wgs_hpo_test/2127020.HPO.txt","size":77,"eventTime":1703107647000}}` is different from expected by the source: `unitycatalog@bgdatabricksstoragev2`.

-------------------------------------

Here is the code to ingest the file from the specified location.

-------------------------------------------------------------------------------------------------------------------

import dlt

from pyspark.sql.functions import col, current_timestamp, split

# Define variables used in code below

file_path = "/Volumes/bgem_dev/wgs_live/hpo/"

username = spark.sql("SELECT regexp_replace(current_user(), '[^a-zA-Z0-9]', '_')").first()[0]

table_name = f"bgem_dev.wgs_live.hpo_test"

checkpoint_path = f"/tmp/{username}/_checkpoint/Live"

# Clear out data from previous demo execution

#spark.sql(f"DROP TABLE IF EXISTS {table_name}")

#dbutils.fs.rm(checkpoint_path, True)

(spark.readStream

.format("cloudFiles")

.option("cloudFiles.format", "text")

.load(file_path)

.select("*", col("_metadata.file_path").alias("source_file"), current_timestamp().alias("processing_time"))

.writeStream

.option("checkpointLocation", checkpoint_path)

.trigger(availableNow=True)

.toTable(table_name)

)

Re: How to ingest files from volume using autoloader

Wojciech_BUK — Fri, 22 Dec 2023 11:40:09 GMT

Hey, i think you are mixing DLT syntaxt with pyspark syntax:

In DLT you should use:

CREATE OR REFRESH STREAMING TABLE
  <table-name>
AS SELECT
  *
FROM
  STREAM read_files(
    '<path-to-source-data>',
    format => '<file-format>'
  )

or in Python

@dlt.table(table_properties={'quality': 'bronze'})
def <table-name>():
  return (
     spark.readStream.format('cloudFiles')
     .option('cloudFiles.format', '<file-format>')
     .load(f'{<path-to-source-data>}')
 )

You don't specified checkpoint for DLT in notebook
checkpoints are stored under the storage location specified in the DLT Pipeline settings

in PySaprk it is ok how you wrote it.

I would avoid dumping your checkpoint to DBFS, create path in your storage and save it there.

Please let me know if that helps or please clarify if you are doing DLT or PySpark

topic Re: How to ingest files from volume using autoloader in Get Started Discussions

How to ingest files from volume using autoloader

Re: How to ingest files from volume using autoloader