英文:
tsql to create extract key and value from JSON data in a serverless sql pool
问题
在Azure Synapse Analytics中使用Serverless SQL pool和T-SQL创建外部表的步骤如下：
首先，你有一个名为'companyDetail'的表，其中包含'employeeInfo'列。以下是创建'companyDetail'表的查询：
CREATE EXTERNAL TABLE companyDetail
(
companyName varchar(100),
employeeInfo varchar(2048)
)
WITH
(
LOCATION = '/all_parquet_files/*.parquet',
DATA_SOURCE = parquet_datasource,
FILE_FORMAT = parquet
)
接下来，根据提供的数据，你需要创建一个名为'employee'的外部表，该表应具有以下结果：
companyName | employeeKey | employeeValue
------------------------------------------
ABC | name | Ramesh
ABC | age | 32 years
ABC | name | Mohan
ABC | experience | 2 years
ABC | name | Dinesh
ABC | age | 39
ABC | experience | 5 years
HIJ | |
DEF | name | Mohit
请使用适当的T-SQL查询来转换和创建这个'employee'外部表，以达到所需的结果。
英文:
How to create external table using Serverless SQL pool using tsql in Azure Synapse Analytics for this scenario:
I have a 'employeeInfo' column in a table called as 'companyDetail'.
This is the query to create 'companyDetail' table:
CREATE EXTERNAL TABLE companyDetail
(
companyName varchar(100),
employeeInfo varchar(2048)
)
WITH
(
LOCATION = '/all_parquet_files/*.parquet',
DATA_SOURCE = parquet_datasource,
FILE_FORMAT = parquet
)
companyDetail table data:
companyName| employeeInfo
----------------------------
ABC | {name: Ramesh, age:32 years}
ABC | {name: Mohan, experience:2 years}
DEF | {name: Dinesh, age:39, experience:5 years}
HIJ |
DEF | {name: Mohit}
I have to create an external table 'employee' from this data which should have this result:
companyName| employeeKey | employeeValue
------------------------------------------
ABC | name | Ramesh
ABC | age | 32 years
ABC | name | Mohan
ABC | experience | 2 years
ABC | name | Dinesh
ABC | age | 39
ABC | experience | 2 years
HIJ | |
DEF | name | Mohit
答案1
得分: 1
以下是已翻译的内容：
一种方法是将外部表中的数据导入临时表或表变量，然后使用导入的数据上的OPENJSON函数将数据翻转为列。 例如：
SELECT companyName, employeeInfo
INTO #tempTable
FROM companyDetail
-- WHERE ...
SELECT companyName, [Key] as employeeKey, [Value] as employeeValue
FROM #tempTable
CROSS APPLY OPENJSON(employeeInfo)
参见：OPENJSON
英文:
One approach would be to import the data from the external table into a temporary table or a table variable, and then use the OPENJSON function on the imported data to flip the data into columns. e.g:
SELECT companyName, employeeInfo
INTO #tempTable
FROM companyDetail
-- WHERE ...
SELECT companyName, [Key] as employeeKey, [Value] as employeeValue
FROM #tempTable
CROSS APPLY OPENJSON(employeeInfo)
see: OPENJSON
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论