Forum Discussion

5 Replies

  • frithjof_v's avatar
    frithjof_v
    Community Champion
    1 year ago

    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-mozart.com)

    • MohammedS's avatar
      MohammedS
      New Member
      1 year ago

      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's avatar
        frithjof_v
        Community Champion
        1 year ago

        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:

         

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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!