my query works with All-purpose cluster but return NULL with SQL Warehouse

[nguyenthuymo]

Hi,

(1) On SQL warehouse, I created a table in unity catalog from data source file vw_businessmetrics_1000.json in ADLS blob.

USE CATALOG `upreport`;

USE SCHEMA `test_genie`;

-- Create the external table from the JSON file

CREATE EXTERNAL TABLE IF NOT EXISTS `vw_BusinessMetrics_1000` (

    Provider STRING,

    School STRING,

    Branch STRING,

    ReportingYear INT,

    SnapshotDate DATE,

    WeekNumber INT,

    CalendarYear INT,

    ProgCode STRING,

    ProgTitle STRING,

    FundingSourceCode STRING,

    StudentType STRING,

    StatusCategory STRING,

    ProgramEnrolmentStatus STRING,

    BusinessData STRING,

    BusinessMeasure STRING,

    Frequency STRING,

    EnrolmentCount NUMERIC(18, 2),

    efts NUMERIC(18, 2),

    Funding NUMERIC(18, 2),

    MOP NUMERIC(18, 2),

    AbandonmentCount NUMERIC(18, 2),

    StudentCount NUMERIC(18, 2)

)

USING JSON

LOCATION 'abfss://.../vw_businessmetrics_1000.json';

(2) Then, I queried this table on SQL warehouse but it return NULL :

SELECT * from upreport.test_genie.vw_businessmetrics_1000 limit 10

(3) I start my all-purpose cluster and query the same query and it show the data correctly.

SELECT * from upreport.test_genie.vw_businessmetrics_1000 limit 10

Anyone knows why my query works with All-purpose cluster but return NULL with SQL Warehouse?

Thank you

  

Cheers

Mo

[Ayushi_Suthar]

Hi @nguyenthuymo , Greetings!

I see you are using a serverless warehouse to query the table where you are not getting the query results.

Can you please give a try to use Pro or classic warehouse to check if you are getting the query result or not? 

Kind Regards,

Ayushi

[nguyenthuymo]

Hi @Ayushi_Suthar 

Thank you very much. I tried with the Classic and Pro and it did not work.

My solution is: drop the table and recreate as a delta table then loading data from json to the delta table. Now it works. Probably, the SQL warehouse only support for delta tables?

Cheers

Mo