topic my query works with All-purpose cluster but return NULL with SQL Warehouse in Data Engineering

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

nguyenthuymo — Mon, 03 Feb 2025 03:21:43 GMT

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

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

Ayushi_Suthar — Tue, 04 Feb 2025 07:45:00 GMT

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

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

nguyenthuymo — Tue, 04 Feb 2025 20:23:30 GMT

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