API to SQL Server 2017
0
I am attempting to write a script to load data from an API to a SQL Server. I want to automate this process so that it can be done weekly or monthly.
I have researched quite extensively and tried to find the best workaround. Here is what I have so far:
DECLARE @json NVARCHAR(MAX)
SET @json = #somehow I need a GET HTTP here, not sure how
SELECT *
FROM OPENJSON(@json)
WITH (id int 'strict $.id',
PageViews nvarchar(50) '$.data.name.page_video_views')
Here is an example of the API data that I am pulling.
{
"data": [{
"name": "page_video_views",
"period": "day",
"values": [{
"value": 634,
"end_time": "2018-11-23T08:00:00+0000"
}, {
"value": 465,
"end_time": "2018-11-24T08:00:00+0000"
}],
"title": "Daily Total Video Views",
"description": "Daily: Total number of times videos have been viewed for more than 3 seconds. (Total Count)"
}
What is the best way to read the data from the API and what is the best way to only select one of the two values that is returned from the API (e.g. 634 vs 465) above?
sql
sql-server tsql asked Nov 26 '18 at 20:58
HaydenHayden
587
add a comment |
0
I am attempting to write a script to load data from an API to a SQL Server. I want to automate this process so that it can be done weekly or monthly.
I have researched quite extensively and tried to find the best workaround. Here is what I have so far:
DECLARE @json NVARCHAR(MAX)
SET @json = #somehow I need a GET HTTP here, not sure how
SELECT *
FROM OPENJSON(@json)
WITH (id int 'strict $.id',
PageViews nvarchar(50) '$.data.name.page_video_views')
Here is an example of the API data that I am pulling.
{
"data": [{
"name": "page_video_views",
"period": "day",
"values": [{
"value": 634,
"end_time": "2018-11-23T08:00:00+0000"
}, {
"value": 465,
"end_time": "2018-11-24T08:00:00+0000"
}],
"title": "Daily Total Video Views",
"description": "Daily: Total number of times videos have been viewed for more than 3 seconds. (Total Count)"
}
What is the best way to read the data from the API and what is the best way to only select one of the two values that is returned from the API (e.g. 634 vs 465) above?
sql
sql-server tsql asked Nov 26 '18 at 20:58
HaydenHayden
587
Which of the two do you want? Also that is not valid JSON. SELECT ISJSON(yourjsonval) returns 0
– dfundako
Nov 26 '18 at 21:09
I would want the second value, 465. And sorry about the JSON, that is just an excerpt of the much larger JSON document
– Hayden
Nov 26 '18 at 21:15
Possible duplicate of Can you call a webservice from TSQL code?
– Nick.McDermaid
Nov 26 '18 at 23:12
2
although that question provides some solutions, not thatsp_OACreateis very old and not recommended. If you must call a web service from T-SQL you're better of using CLR (which also has it's own issues). The best way to do it is have something external that calls the web service then pushes it in... like maybe powershell. Here's something that does that backwards: stackoverflow.com/questions/35615803/…
– Nick.McDermaid
Nov 26 '18 at 23:16
add a comment |
0
0
0
I am attempting to write a script to load data from an API to a SQL Server. I want to automate this process so that it can be done weekly or monthly.
I have researched quite extensively and tried to find the best workaround. Here is what I have so far:
DECLARE @json NVARCHAR(MAX)
SET @json = #somehow I need a GET HTTP here, not sure how
SELECT *
FROM OPENJSON(@json)
WITH (id int 'strict $.id',
PageViews nvarchar(50) '$.data.name.page_video_views')
Here is an example of the API data that I am pulling.
{
"data": [{
"name": "page_video_views",
"period": "day",
"values": [{
"value": 634,
"end_time": "2018-11-23T08:00:00+0000"
}, {
"value": 465,
"end_time": "2018-11-24T08:00:00+0000"
}],
"title": "Daily Total Video Views",
"description": "Daily: Total number of times videos have been viewed for more than 3 seconds. (Total Count)"
}
What is the best way to read the data from the API and what is the best way to only select one of the two values that is returned from the API (e.g. 634 vs 465) above?
sql
sql-server tsql asked Nov 26 '18 at 20:58
HaydenHayden
587
I am attempting to write a script to load data from an API to a SQL Server. I want to automate this process so that it can be done weekly or monthly.
I have researched quite extensively and tried to find the best workaround. Here is what I have so far:
DECLARE @json NVARCHAR(MAX)
SET @json = #somehow I need a GET HTTP here, not sure how
SELECT *
FROM OPENJSON(@json)
WITH (id int 'strict $.id',
PageViews nvarchar(50) '$.data.name.page_video_views')
Here is an example of the API data that I am pulling.
{
"data": [{
"name": "page_video_views",
"period": "day",
"values": [{
"value": 634,
"end_time": "2018-11-23T08:00:00+0000"
}, {
"value": 465,
"end_time": "2018-11-24T08:00:00+0000"
}],
"title": "Daily Total Video Views",
"description": "Daily: Total number of times videos have been viewed for more than 3 seconds. (Total Count)"
}
What is the best way to read the data from the API and what is the best way to only select one of the two values that is returned from the API (e.g. 634 vs 465) above?
sql
sql-server tsql sql
sql-server tsql asked Nov 26 '18 at 20:58
HaydenHayden
587
asked Nov 26 '18 at 20:58
HaydenHayden
587
asked Nov 26 '18 at 20:58
HaydenHayden
587
asked Nov 26 '18 at 20:58
HaydenHayden
587
asked Nov 26 '18 at 20:58
HaydenHayden
587
587
Which of the two do you want? Also that is not valid JSON. SELECT ISJSON(yourjsonval) returns 0
– dfundako
Nov 26 '18 at 21:09
I would want the second value, 465. And sorry about the JSON, that is just an excerpt of the much larger JSON document
– Hayden
Nov 26 '18 at 21:15
Possible duplicate of Can you call a webservice from TSQL code?
– Nick.McDermaid
Nov 26 '18 at 23:12
2
although that question provides some solutions, not thatsp_OACreateis very old and not recommended. If you must call a web service from T-SQL you're better of using CLR (which also has it's own issues). The best way to do it is have something external that calls the web service then pushes it in... like maybe powershell. Here's something that does that backwards: stackoverflow.com/questions/35615803/…
– Nick.McDermaid
Nov 26 '18 at 23:16
add a comment |
Which of the two do you want? Also that is not valid JSON. SELECT ISJSON(yourjsonval) returns 0
– dfundako
Nov 26 '18 at 21:09
I would want the second value, 465. And sorry about the JSON, that is just an excerpt of the much larger JSON document
– Hayden
Nov 26 '18 at 21:15
Possible duplicate of Can you call a webservice from TSQL code?
– Nick.McDermaid
Nov 26 '18 at 23:12
2
although that question provides some solutions, not thatsp_OACreateis very old and not recommended. If you must call a web service from T-SQL you're better of using CLR (which also has it's own issues). The best way to do it is have something external that calls the web service then pushes it in... like maybe powershell. Here's something that does that backwards: stackoverflow.com/questions/35615803/…
– Nick.McDermaid
Nov 26 '18 at 23:16
Which of the two do you want? Also that is not valid JSON. SELECT ISJSON(yourjsonval) returns 0
– dfundako
Nov 26 '18 at 21:09
Which of the two do you want? Also that is not valid JSON. SELECT ISJSON(yourjsonval) returns 0
– dfundako
Nov 26 '18 at 21:09
I would want the second value, 465. And sorry about the JSON, that is just an excerpt of the much larger JSON document
– Hayden
Nov 26 '18 at 21:15
I would want the second value, 465. And sorry about the JSON, that is just an excerpt of the much larger JSON document
– Hayden
Nov 26 '18 at 21:15
Possible duplicate of Can you call a webservice from TSQL code?
– Nick.McDermaid
Nov 26 '18 at 23:12
Possible duplicate of Can you call a webservice from TSQL code?
– Nick.McDermaid
Nov 26 '18 at 23:12
2
2
although that question provides some solutions, not that
sp_OACreate is very old and not recommended. If you must call a web service from T-SQL you're better of using CLR (which also has it's own issues). The best way to do it is have something external that calls the web service then pushes it in... like maybe powershell. Here's something that does that backwards: stackoverflow.com/questions/35615803/…– Nick.McDermaid
Nov 26 '18 at 23:16
although that question provides some solutions, not that
sp_OACreate is very old and not recommended. If you must call a web service from T-SQL you're better of using CLR (which also has it's own issues). The best way to do it is have something external that calls the web service then pushes it in... like maybe powershell. Here's something that does that backwards: stackoverflow.com/questions/35615803/…– Nick.McDermaid
Nov 26 '18 at 23:16
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53488960%2fapi-to-sql-server-2017%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53488960%2fapi-to-sql-server-2017%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Which of the two do you want? Also that is not valid JSON. SELECT ISJSON(yourjsonval) returns 0
– dfundako
Nov 26 '18 at 21:09
I would want the second value, 465. And sorry about the JSON, that is just an excerpt of the much larger JSON document
– Hayden
Nov 26 '18 at 21:15
Possible duplicate of Can you call a webservice from TSQL code?
– Nick.McDermaid
Nov 26 '18 at 23:12
2
although that question provides some solutions, not that
sp_OACreateis very old and not recommended. If you must call a web service from T-SQL you're better of using CLR (which also has it's own issues). The best way to do it is have something external that calls the web service then pushes it in... like maybe powershell. Here's something that does that backwards: stackoverflow.com/questions/35615803/…– Nick.McDermaid
Nov 26 '18 at 23:16