External table Creation in Fabric

[MohammedS]

Hi,

I wanted to create an external table in fabric/ADLS and file type is Parquet.

Not getting to find any article which is useful , all the sql query which is written is giving syntax error:

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-as-select-transact-sql?...

please help on this

[jovanpop-msft]

@MohammedS if you need an external table for Parquet vote for this idea: https://community.fabric.microsoft.com/t5/Fabric-Ideas/Support-external-tables-for-parquet-csv-in-Fa...

[Anonymous]

Hi @MohammedS 

The statements you are referring to are in the SQL Server 2022 documentation. It's likely that Fabric doesn't support these statements. When you switch the documentation to the Fabric version, you will notice that these statements are not available.

Although Fabric provides many SQL Server features through Data warehouse and SQL analytics endpoint, it doesn't support all of the table features offered by SQL Server at this time. According to this documentation, External tables are not currently supported in Data warehouse and SQL analytics endpoint in Fabric.

So if you want to experience with external tables in Fabric, you can create them in a lakehouse via a notebook. You could refer to @frithjof_v 's reply. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[frithjof_v]

I'm curious why you want to create an external table?

In general, the preferred format in Fabric is a managed delta table. This ensures the best interoperability across the Fabric platform. 

For reference, sample code to create an external table with delta format is shown below, however in general I would choose to use a managed delta table:

%%sql
CREATE TABLE FactInternetSales_ext (
CustomerID STRING, 
ProductID STRING, 
CurrencyID STRING,
SalesAmount STRING,
....
) USING DELTA OPTIONS (path 'Files/FactInternetSales_ext');

Source: Working with tables in Microsoft Fabric Lakehouse - Everything you need to know! - Data Mozart (data...

[MohammedS]

I am facing an identifier issue when using a shortcut table in Fabric for a Parquet file, which is preventing the table from being displayed. To resolve this, I want to create an external table, as it could potentially fix the problem.

I will definitely try the approach you shared, but please provide an alternative approach that can address this issue.

Thank you.

[frithjof_v]

I'm curious why you got the identifier issue. Did the error message tell more?
(In case you share more details, please don't share any sensitive or secret information).

Regarding the external table with parquet as a source - this worked for me:

spark.sql("""
CREATE EXTERNAL TABLE IF NOT EXISTS table_name (
    column_name COLUMN_TYPE,
    another_column_name COLUMN_TYPE,
    another_column_name COLUMN_TYPE
)
STORED AS PARQUET
LOCATION 'Files/folder_name/another_folder_name'
""")

Here is another one, very similar: