Forum Discussion

kobia10's avatar
kobia10
Microsoft Employee
2 years ago

Create a table shortcut to CSV folder using DDL 'Create Table' stopped working

For the last few months we ran a statement in a notebook to create a table under the tables folder

The table was created correctly and we could run pySpark code against the table

Very recently, after executing the statement we get table created with a red X sign to its right. Cannot see the fields. Pressing on the X shows an error that the target table cannot be reached. 

However, I can view the data against that table from a cell using pySpark

 

CREATE EXTERNAL TABLE IF NOT EXISTS `msdyn_facility` (
`Id` STRING,
`SinkCreatedOn` TIMESTAMP,
`SinkModifiedOn` TIMESTAMP,
`statecode` INTEGER,
`statuscode` INTEGER,
`msdyn_approvalstatus` INTEGER,
`msdyn_waterriskindex` STRING,
`msdyn_waterriskindex_entitytype` STRING,
`modifiedonbehalfby` STRING,
`modifiedonbehalfby_entitytype` STRING,
`owninguser` STRING,
`owninguser_entitytype` STRING,
`createdonbehalfby` STRING,
`createdonbehalfby_entitytype` STRING,
`msdyn_dataconnectionrefresh` STRING,
`msdyn_dataconnectionrefresh_entitytype` STRING,
`owningbusinessunit` STRING,
`owningbusinessunit_entitytype` STRING,
`owningteam` STRING,
`owningteam_entitytype` STRING,
`modifiedby` STRING,
`modifiedby_entitytype` STRING,
`createdby` STRING,
`createdby_entitytype` STRING,
`msdyn_facilitytype` STRING,
`msdyn_facilitytype_entitytype` STRING,
`msdyn_dataconnection` STRING,
`msdyn_dataconnection_entitytype` STRING,
`msdyn_waterbasin` STRING,
`msdyn_waterbasin_entitytype` STRING,
`ownerid` STRING,
`ownerid_entitytype` STRING,
`createdonbehalfbyyominame` STRING,
`msdyn_addresscountryisocode` STRING,
`owneridname` STRING,
`msdyn_facilityid` STRING,
`msdyn_addresscounty` STRING,
`msdyn_name` STRING,
`msdyn_addresscity` STRING,
`msdyn_waterriskindexname` STRING,
`importsequencenumber` INTEGER,
`msdyn_dataconnectionname` STRING,
`modifiedbyyominame` STRING,
`msdyn_longitude` DECIMAL(38,10),
`msdyn_addressstateprovince` STRING,
`msdyn_addresszippostalcode` STRING,
`msdyn_facilitytypename` STRING,
`utcconversiontimezonecode` INTEGER,
`createdbyyominame` STRING,
`msdyn_latitude` DECIMAL(38,10),
`modifiedbyname` STRING,
`timezoneruleversionnumber` INTEGER,
`owneridtype` STRING,
`msdyn_addressstreet1` STRING,
`owneridyominame` STRING,
`modifiedon` TIMESTAMP,
`msdyn_origincorrelationid` STRING,
`modifiedonbehalfbyyominame` STRING,
`createdbyname` STRING,
`createdon` TIMESTAMP,
`msdyn_dataconnectionrefreshname` STRING,
`owningbusinessunitname` STRING,
`createdonbehalfbyname` STRING,
`modifiedonbehalfbyname` STRING,
`msdyn_waterbasinname` STRING,
`versionnumber` INTEGER,
`msdyn_addressstreet2` STRING,
`overriddencreatedon` TIMESTAMP)
USING CSV
OPTIONS (
`multiLine` 'true',
`ignoreLeadingWhiteSpace` 'true',
`quote` '"',
`timestampFormat` 'M/dd/yyyy hh:mm:ss a',
`ignoreTrailingWhiteSpace` 'true',`escape` '"')
LOCATION 'Files/dv/msdyn_facility/*.csv'
TBLPROPERTIES (
'sql:IsAppendOnly' = 'true');
data engineering
one lake

8 Replies

    • kobia10's avatar
      kobia10
      Microsoft Employee
      2 years ago

      No. The table is a new one.

      Used to work before without any issues. Not clear when the change in Fabric has occured.

      As I said i can use pySpark to query the table in a notebook. Just the Lakehouse UI seems to be broken

       

      This works and returns data...

      df = spark.sql("SELECT * FROM Sust.msdyn_facility LIMIT 1000")
      display(df)
       
      See below the error saying 'The requested file cannot be found' relating to the shortcut that returns data
       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi kobia10 ,

        Thanks for using Fabric Community.
        I would like to understand, what changes you made recently?
        When did you started observing this issue?
        Are you able to load the table in Notebooks? Sql Endpoint?

        I can guide you, if you can share these details.