SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Consuming JSON Formatted API Data in 2016

By Ryan Loftin,

There are new OPENJSON and FOR JSON commands in SQL Server 2016. Let's see if we can use OPENJSON to grab publically available data from the thousands of JSON formatted API's across the internet.  Let's create the sample Blockchain database with the btc.Blocks table.

USE [master]
GO

CREATE DATABASE [Blockchain]
GO
ALTER DATABASE [Blockchain] SET COMPATIBILITY_LEVEL = 130
GO

EXEC sys.sp_db_vardecimal_storage_format N'Blockchain', N'ON'
GO

USE [Blockchain]
GO

CREATE SCHEMA [btc]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [btc].[Blocks](
	[BlockID] 		[int] 		NOT NULL,
	[BlockVersion] 		[int] 		NULL,
	[BlockHash] 		[nvarchar](32) 	NULL,
	[PreviousBlockHash] 	[nvarchar](32) 	NULL,
	[BlockTimestamp] 	[datetime] 	NULL,
PRIMARY KEY CLUSTERED 
(
	[BlockID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [master]
GO
ALTER DATABASE [Blockchain] SET  READ_WRITE 
GO

In order to reach the API, we need to enable Ole Automation to open up the sp_OA system stored procedures

From there we will create two functions.  The first function will allow you to enter the block height of BlockChain.info's API.  To see the data in your browser, you can click on this link: https://blockchain.info/block-height/0?format=json.  The block height that will pull is block 0, Satoshi's genesis block.  You can change the 0 in the URL to pull any block up to the current block height (a little over 400,000 blocks).

Unfortunately BlockChain.info stores the original UNIX timestamp in the blockXXX.dat data files from the chain.  In order to convert this to SQL Server datetime, the script below will add and a second function (btc.fn_UNIXConvertToDateTime) for the conversion.

-- Enable Ole Automation
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO

-- This function will use the CLR system stored procedures to make a request to the API url and bring data back as an NVARCHAR(4000) variable
CREATE FUNCTION [btc].[fn_GetBlockFromAPI]
(@BlockHeight INT)

RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @url	VARCHAR(255);
    DECLARE @obj	INT;
    DECLARE @hr		INT;
    DECLARE @msg	VARCHAR(8000);	
    DECLARE @json	NVARCHAR(4000);	--Cannot use MAX with CLR stored procedures

    SET @url = 'https://blockchain.info/block-height/' + CAST(@BlockHeight AS VARCHAR(255)) + '?format=json';

    EXEC @hr = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @obj OUT  -- Creates an instance of an OLE object
    IF @hr <> 0 BEGIN SET @Msg = 'sp_OACreate WinHttp.WinHttpRequest.5.1 failed. Cannot connect to Blockchain.info' GOTO Error END

    EXEC @hr = sp_OAMethod @obj, 'Open', NULL, 'GET', @Url, false  -- Calls a method of an OLE object.
    IF @hr <> 0 BEGIN SET @msg = 'sp_OAMethod Open failed. Cannot connect to Blockchain.info' GOTO Error END

    EXEC @hr = sp_OAMethod @obj, 'SetRequestHeader', NULL, 'Content-Type', 'multipart/form-data'
    IF @hr <> 0 BEGIN SET @msg = 'sp_OAMethod SetRequestHeader failed. Cannot connect to Blockchain.info' GOTO Error END

    EXEC @hr = sp_OAMethod @obj, SEND, NULL, ''
    IF @hr <> 0 BEGIN SET @msg = 'sp_OAMethod Send failed. Cannot connect to Blockchain.info' GOTO Error END

    EXEC @hr = sp_OAGetProperty @Obj, 'ResponseText', @json OUTPUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @Obj

    EXEC @hr = sp_OADestroy @obj

    RETURN @json

    Error:
    EXEC @hr = sp_OADestroy @obj
    RETURN @msg
END
GO

-- API is holding UNIX time, so we will need to convert to SQL Server datetime format with this function
CREATE FUNCTION [btc].[fn_UNIXConvertToDateTime] (@DateTime BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetime BIGINT;
    SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
    SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset
    RETURN (SELECT DATEADD(SECOND,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS DATETIME)))
END;

Now lets call the btc.fn_GetBlockFromAPI function to grab the JSON data and insert into the @Block temp table.  From there we will insert the @Block data into the btc.Blocks table IF (SELECT MainChain FROM @BlcokTemp) = 1.  These are main chain blocks (not orphaned) and we will also convert the nasty UNIX timestamp.

DECLARE @BlockHeight INT;
SET @BlockHeight = 0		-- Height of block on the chain you want data for

DECLARE @Block VARCHAR(MAX);
SET @Block = (SELECT [btc].[fn_GetBlockFromAPI](@BlockHeight));

DECLARE @BlockTemp TABLE(
	[BlockID]		INT		NOT NULL,
	[BlockVersion]		INT		NOT NULL,
	[BlockHash]		VARCHAR(32)	NOT NULL,
	[PreviousBlockHash] 	VARCHAR(32)	NOT NULL,
	[BlockTimestamp]	VARCHAR(10)	NOT NULL,
	[MainChain]		BIT		NOT NULL
);

INSERT INTO @BlockTemp ([BlockID], [BlockVersion], [BlockHash], [PreviousBlockHash], [BlockTimestamp], [MainChain]) 
SELECT * 
FROM OPENJSON (@Block, '$.blocks')
WITH ( 
        BlockID			INT		'$.height',
	BlockVersion		INT		'$.ver',
        BlockHash		VARCHAR(32)	'$.hash',
	PreviousBlockHash	VARCHAR(32)	'$.prev_block',
	BlockTimestamp		VARCHAR(10)	'$.time',
	MainChain		BIT		'$.main_chain'
	)

-- Now only INSERT the data from the temp table if MainChain = 1 (True) and also convert the UNIX timestamp
IF (SELECT MainChain FROM @BlockTemp) = 1
BEGIN
	INSERT INTO btc.Blocks ([BlockID], [BlockVersion], [BlockHash], [PreviousBlockHash], [BlockTimestamp])
	SELECT [BlockID], [BlockVersion], [BlockHash], [PreviousBlockHash], btc.fn_UNIXConvertToDateTime([BlockTimestamp])
	FROM @BlockTemp
END

Result set from the INSERT statement:

Result Set

Of course you can add a loop to pull rows sequentially, but some API providers are sensitive about their load.  If the API provider is hit too hard they may ban your IP, unless you space out your API calls or sign up for and API Code to bypass limits.

Disadvantages:

  • One major restriction to be aware of is the 4000 character limit of sp_OACreate.  If you need to pull JSON data from an API that may have over 4000 characters, the result set will return NULL.  However, this process can be highly effective for small amounts of data you need to consume frequently if you know the JSON data source will never exceed 4000 characters.
  • Must turn on Ole Automation, be sure to check your security needs and settings. 

Advantages:

  • Bypasses Python, Java, PHP, Ruby, etc libraries to reach the JSON target (and the devs that go with them)
  • Can be implemented and managed by the DBA or SQL Developer natively within SQL Server 2016

Now that you know how to pull data from an API natively, you can search for publicly available data from the world largest API directory.  This tool should be great for data warehousing or reporting needs to compare public data with your private date to see how they benchmark.

 
Total article views: 1911 | Views in the last 30 days: 6
 
Related Articles
FORUM

Blocked and Blocking process Help

Blocked and Blocking process Help

FORUM

Different Behaviour of VARCHAR and NVARCHAR

Variables declared with VARCHAR and NVARCHAR were behaviouring differently.

FORUM

Blocking

how to Create total information about blocking using sys.dm_os_waiting_tasks.

FORUM

Tricky ...VARCHAR

VARCHAR logics

FORUM

blocking

blocking

Tags
json    
sql server 2016    
sqlclr    
 
Contribute