cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

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

nguyenthuymo
New Contributor II

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
databricks_question_2.png

โ€ƒโ€ƒ

databricks_question.png
 
Cheers
Mo
2 REPLIES 2

Ayushi_Suthar
Databricks Employee
Databricks Employee

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
New Contributor II

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

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group