Forum Discussion

frithjof_v's avatar
frithjof_v
Community Champion
2 years ago
Solved

External table not available in SQL Analytics Endpoint or Power BI

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! 

data engineering
Lakehouse
Notebook
  • TomMartens's avatar
    TomMartens
    2 years ago

    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

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    2 years ago

    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's avatar
    frithjof_v
    Community Champion
    2 years ago

    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