External table not available in SQL Analytics Endpoint or Power BI

[frithjof_v]

Hi,

I used a Notebook to create an external table (unmanaged table) in my Lakehouse based on a txt file in my Lakehouses' File folder:

%%sql

CREATE TABLE testtxtexternal

(

    name STRING,

    price INT,

    category STRING

)

USING CSV

OPTIONS (

    path 'Files/txttest.txt',

    delimiter ';',

    header 'true',

    inferSchema 'true'

)

I can then successfully query the table from a Notebook, e.g. by using:

%%sql

SELECT * FROM testtxtexternal

However the table is not available in the SQL Analytics Endpoint and also not available in Power BI Desktop.

Is this the expected behavior? 

I am just curious.

The ambition is to be able to write SQL queries against a txt file, preferably from SQL Analytics Endpoint.

When I tested, the txt file was uploaded to the files section of the Lakehouse.

However in the real case we want to have the txt file in ADLS Gen2 and shortcut the file into the Lakehouse for SQL querying.

Thank you in advance! 

[TomMartens · Accepted solution]

Hey @frithjof_v ,

the reason why the table does not appear is the format of the source file. The lingua franca for everything Microsoft Fabric is the Delta Table format. If you want to shortcut to the txt file, then you need a step that reads the txt file into a dataframe and writes the dataframe as a Delta Table.

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[frithjof_v]

Thank you @TomMartens,

I guess that means we will need to trigger (or schedule) some ETL tool (e.g. Data pipeline, Dataflow Gen2, Notebook, etc.) if we want to bring in updated data from the .txt file into SQL Analytics Endpoint (as it requires the data to be in Delta table format).

Perhaps we can use Storage Event Trigger to trigger the ETL each time the .txt file gets updated in ADLS Gen2, and update the Delta table.

Best regards, Frithjof