February 1, 2018 at 8:49 am
Hi folks,
I'm importing AWS data (json files) into SQL. Each file contains data on one single Amazon instance. So basically one row in the table per file ingested. Eventually I'll want to convert it into a SP that can handle different files going into different tables hence the dynamic SQL. Everything is working fine but the code as it is can run into some issues if the column list order differs from the ones being pulled in from the JSON file.
Here is a fully functional version of the code. I've commented some of parts that wouldn't work on your own rig but left it in so people can see what the original intent is.
DECLARE @json NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
DECLARE @repeat NVARCHAR(MAX)
--DECLARE @columns TABLE (Column_Name NVARCHAR(255))
DECLARE @delimited NVARCHAR(MAX)
-----------------------------------------------------------
--This is how I'm getting the columns into a delimited list
-----------------------------------------------------------
--INSERT INTO @columns (Column_Name)
--SELECT COLUMN_NAME FROM json_test.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'aws_base' AND COLUMN_NAME <> 'ID'
--SELECT @delimited = ISNULL(@delimited, '') + Column_Name + ', ' FROM @columns
--SET @delimited = SUBSTRING(@delimited, 1, LEN(@delimited)-1)
-----------------------------------------------------------
--but for demo purposes we will just list out the columns
-----------------------------------------------------------
DECLARE @columns NVARCHAR(MAX) = 'PlatformName, PlatformVersion, AgentVersion, ComputerName'
CREATE TABLE #tmp (aws_name NVARCHAR(50), aws_value NVARCHAR(1000))
-----------------------------------------------------------
--grabbing the json file
-----------------------------------------------------------
--SET @json = (
--SELECT BulkColumn
--FROM OPENROWSET (BULK 'D:\JSON\somefile.json', SINGLE_CLOB) AS j
--)
-----------------------------------------------------------
--more sample data
-----------------------------------------------------------
SET @json =
N'
{ "PlatformName" : "Amazon Linux AMI","PlatformVersion": "2017.09", "AgentVersion": "2.2.120.0", "ComputerName":"Server1" }
'
INSERT INTO #tmp (aws_name, aws_value)
SELECT , [value] FROM OPENJSON(@json)
--SELECT * FROM #tmp
SET @repeat = (SELECT ' , MAX(CASE WHEN aws_name = ' + '''' + CAST( aws_name AS NVARCHAR(50)) + '''' + ' THEN aws_value END) AS ' + '''' + CAST( aws_name AS NVARCHAR(50)) + ''''
FROM #tmp FOR XML PATH(''),TYPE).value('./text()[1]', 'nvarchar(MAX)')
SET @repeat = RIGHT(@repeat, LEN(@repeat)-2)
-----------------------------------------------------------
--insert into actual table
-----------------------------------------------------------
--SELECT @sql =
-- 'INSERT INTO aws_base (' + @delimited + ')' +
-- ' SELECT ' + @repeat +
-- ' FROM #tmp'
-----------------------------------------------------------
--let's put in a temp table for demo purposes
-----------------------------------------------------------
CREATE TABLE #aws_base (PlatformName NVARCHAR(100), PlatformVersion NVARCHAR(100), AgentVersion NVARCHAR(100), ComputerName NVARCHAR(100))
SELECT @sql =
' INSERT INTO #aws_base (' + @columns + ')' +
' SELECT ' + @repeat +
' FROM #tmp'
EXECUTE (@sql)
--Verify Data
SELECT * FROM #aws_base
DROP TABLE #tmp
DROP TABLE #aws_base
I'm a little rusty after being away from SQL for about a year but I feel like there should be a simple solution that I'm completely missing. I just want to make sure the column list in the INSERT INTO command will be in the same order as the columns in the SELECT statement.
February 1, 2018 at 10:18 am
Not sure why you are using dynamic SQL is the structure of the table aws_base is fixed. If I know the column names of the table and I know that the JSON string is going to have the values needed for the columns defined in the table, I don't need to use dynamic SQL. See the following code:
DECLARE @json NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)
DECLARE @repeat NVARCHAR(MAX)
--DECLARE @columns TABLE (Column_Name NVARCHAR(255))
DECLARE @delimited NVARCHAR(MAX)
SET @json =
N'
{ "PlatformName" : "Amazon Linux AMI","PlatformVersion": "2017.09", "AgentVersion": "2.2.120.0", "ComputerName":"Server1" }
'
SELECT , [value] FROM OPENJSON(@json);
IF OBJECT_ID('tempdb..#aws_base','U') IS NOT NULL
DROP TABLE #aws_base;
CREATE TABLE #aws_base (PlatformName NVARCHAR(100), PlatformVersion NVARCHAR(100), AgentVersion NVARCHAR(100), ComputerName NVARCHAR(100));
INSERT INTO [#aws_base]([PlatformName],[PlatformVersion],[AgentVersion],[ComputerName])
SELECT
MAX(CASE WHEN = 'PlatformName' THEN [value] ELSE '' END)
, MAX(CASE WHEN = 'PlatformVersion' THEN [value] ELSE '' END)
, MAX(CASE WHEN = 'AgentVersion' THEN [value] ELSE '' END)
, MAX(CASE WHEN = 'ComputerName' THEN [value] ELSE '' END)
FROM
OPENJSON(@json) AS [oj];
SELECT * FROM [#aws_base] AS [ab];
SET @json =
N'
{ "PlatformVersion": "2017.09", "PlatformName" : "Amazon Linux AMI", "AgentVersion": "2.2.120.0", "ComputerName":"Server2" }
';
SELECT , [value] FROM OPENJSON(@json);
INSERT INTO [#aws_base]([PlatformName],[PlatformVersion],[AgentVersion],[ComputerName])
SELECT
MAX(CASE WHEN = 'PlatformName' THEN [value] ELSE '' END)
, MAX(CASE WHEN = 'PlatformVersion' THEN [value] ELSE '' END)
, MAX(CASE WHEN = 'AgentVersion' THEN [value] ELSE '' END)
, MAX(CASE WHEN = 'ComputerName' THEN [value] ELSE '' END)
FROM
OPENJSON(@json) AS [oj];
SELECT * FROM [#aws_base] AS [ab];
SET @json =
N'
{ "PlatformVersion": "2017.09", "PlatformName" : "Amazon Linux AMI", "AgentVersion": "2.2.120.0", "ComputerName":"Server3", "AnotherUnusedParameter":"Unused" }
';
SELECT , [value] FROM OPENJSON(@json);
INSERT INTO [#aws_base]([PlatformName],[PlatformVersion],[AgentVersion],[ComputerName])
SELECT
MAX(CASE WHEN = 'PlatformName' THEN [value] ELSE '' END)
, MAX(CASE WHEN = 'PlatformVersion' THEN [value] ELSE '' END)
, MAX(CASE WHEN = 'AgentVersion' THEN [value] ELSE '' END)
, MAX(CASE WHEN = 'ComputerName' THEN [value] ELSE '' END)
FROM
OPENJSON(@json) AS [oj];
SELECT * FROM [#aws_base] AS [ab];
February 1, 2018 at 10:57 am
Yes, I know I don’t have to use Dynamic code for a single file that all have the same structure. But I mentioned in my post that I wanted to reuse the code. The reason for that is that a single instance can produce several JSON files all with their own structure. These will all go into separate tables.
February 1, 2018 at 11:13 am
Y.B. - Thursday, February 1, 2018 10:57 AMYes, I know I don’t have to use Dynamic code for a single file that all have the same structure. But I mentioned in my post that I wanted to reuse the code. The reason for that is that a single instance can produce several JSON files all with their own structure. These will all go into separate tables.
Then I would use separate queries unless you have a really dynamic environment. Given a choice I would rather stay away from dynamic SQL, using dynamic SQL where I actually need to because of the nature of the application/database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply