はじめに
以前YouTube APIを利用する記事をいくつか投稿しましたが、今回もまたそれに関連する記事です。
今回は、自分のYouTubeチャンネルの情報をローカルのデータベースに保存して、それを利用してレポートを作成してみようと思います。
YouTubeチャンネルからPlaylistの情報と各Playlistに属するVideoの情報を取得し、保存したいと思います。
前もってどのような情報が取得可能かをチェックし、以下のようなデータベースを準備しました。
Playlist用のテーブルとVideo用のテーブルが見えますね。
データを扱うならSharperlightっていうことで、早速Sharperlightでのソリューションの構築と参りましょう。
ソリューション
構築するソリューションを図にするとこんな感じになるのかな。
データモデル
データモデルの作成はこの記事を参照してね。
で、このようなデータモデルが出来ました。
基本情報はこんな感じ。
スキーマの定義はここ。JOINも追加してみました。
これでデータモデルの準備はOK。
スケジュールタスク
ここではYouTube APIを利用してデータを取得する処理を、Sharperlightスケジュールタスクを利用して作成します。
YouTube APIを利用したタスクの作成はこの記事を参照してください。
そして、今回はそのタスクを編集して使用してみます。
処理の流れは、先ずPlailistの一覧を取得して、Playlist用のテーブルに保存。そのテーブルから保存されているPlaylistIDを取得して、そのID毎にループ処理を行いVideoの情報を取得しテーブルに保存していきます。
- このアクションでは、Google API KeyやChannelIDなどのパラメータを準備しています。
- 子タスクを呼び出しています。子タスクはYouTube APIからPlaylist一覧情報を取得して、テーブル[dbo].[YouTubePlayLists]に保存します。
- Sharperlightクエリーを定義して、テーブル[dbo].[YouTubePlayLists]からPlaylist情報を読み取ります。またこのアクションはFor文の役目を果たします。クエリによって抽出されたデータを一行ずつ次のアクションに渡すことができます。
- アクション3によって渡されるPlaylistIDを利用して、今度はYouTube APIからそのPlaylistに含まれるVideoの全情報を取得し、テーブル[dbo].[YouTubeVideos]に保存します。
- このアクションは、単にForループの終わりを示すアクションです。クエリの組み合わせアクションと対を成します。クエリの組み合わせアクションとクエリの組み合わせ終了アクションの間に定義されたアクションが、クエリの組み合わせアクション内に定義されたクエリにより抽出されるデータセットの行数分繰り返されるというイメージです。
参考までに。コード中のこういった表記 lib_task.ParamGet(), lib_sys.urlContent.Get() は、SharperlightのJavaScript関数です。タスクに渡されるパラメータの値を取得したり、Getリクエストをかけたりする関数です。
又、エラーハンドリングは省略しました。
// Playlistの情報を取得します。
var nextPageToken = "";
var url = lib_task.ParamGet('BaseAPIUrl') + "&maxResults=" + lib_task.ParamGet('MaxResultPerPage') + "&channelId=" + lib_task.ParamGet('ChannelId') + "&key=" + lib_task.ParamGet('GoogleAPIKey');
var xUrl = "";
for (var p=0; p<10; p++){
if (nextPageToken == ""){
xUrl = url;
}else{
xUrl = url + "&pageToken=" + nextPageToken;
}
console.log("P=" + p + ": " + xUrl);
let ytSnippet = lib_sys.urlContent.Get(xUrl);
let ytSnippetObj = JSON.parse(ytSnippet);
nextPageToken = ytSnippetObj.nextPageToken;
let content = "";
if (ytSnippetObj.items.length == 0) { break; }
for (var i=0; i<ytSnippetObj.items.length; i++){
_wb(ytSnippetObj.items[i]);
}
if (typeof nextPageToken === "undefined") { break; }
}
function _wb(item){
var updateStatement = "UPDATE [dbo].[YouTubePlayLists] set \r\n \
[ChannelId] = '<<ChannelId>>' \r\n \
,[Title] = N'<<Title>>' \r\n \
,[ChannelTitle] = N'<<ChannelTitle>>' \r\n \
,[PublishedAt] = '<<PublishedAt>>' \r\n \
,[RefreshedAt] = getdate() \r\n \
WHERE [Id] = '<<Id>>';";
var insertStatement = "\r\n IF @@ROWCOUNT=0 \r\n \
BEGIN \r\n \
insert into [dbo].[YouTubePlayLists] (\r\n \
[Id], [ChannelId], [Title], [ChannelTitle], [IsEnabled], [PublishedAt], [RefreshedAt]) \r\n \
VALUES ('<<Id>>', '<<ChannelId>>', N'<<Title>>', N'<<ChannelTitle>>', 0, '<<PublishedAt>>', getdate()); \r\n \
END ";
var ups = updateStatement.replace('<<ChannelId>>', item.snippet.channelId);
ups = ups.replace('<<Title>>', item.snippet.title.replace(',','@@@').replace(/'/g, "''"));
ups = ups.replace('<<ChannelTitle>>', item.snippet.channelTitle.replace(',','@@@').replace(/'/g, "''"));
ups = ups.replace('<<PublishedAt>>', item.snippet.publishedAt);
ups = ups.replace('<<Id>>', item.id);
var ins = insertStatement.replace('<<Id>>', item.id);
ins = ins.replace('<<ChannelId>>', item.snippet.channelId);
ins = ins.replace('<<Title>>', item.snippet.title.replace(',','@@@').replace(/'/g, "''"));
ins = ins.replace('<<ChannelTitle>>', item.snippet.channelTitle.replace(',','@@@').replace(/'/g, "''"));
ins = ins.replace('<<PublishedAt>>', item.snippet.publishedAt);
var sqlStatement = ups + ins;
console.log(sqlStatement);
lib_app.sql.Execute(lib_task.UserSessionIdGet(),'YOUTUBE', sqlStatement);
}
// Playlist毎に属するVideoの情報を取得します。
var nextPageToken = "";
var url = lib_task.ParamGet('BaseAPIUrl') + "&maxResults=" + lib_task.ParamGet('MaxResultPerPage') + "&playlistId=" + "{%Id}" + "&key=" + lib_task.ParamGet('GoogleAPIKey');
var xUrl = "";
for (var p=0; p<10; p++){
if (nextPageToken == ""){
xUrl = url;
}else{
xUrl = url + "&pageToken=" + nextPageToken;
}
console.log("P=" + p + ": " + xUrl);
let ytSnippet = lib_sys.urlContent.Get(xUrl);
let ytSnippetObj = JSON.parse(ytSnippet);
nextPageToken = ytSnippetObj.nextPageToken;
let content = "";
if (ytSnippetObj.items.length == 0) { break; }
for (var i=0; i<ytSnippetObj.items.length; i++){
-_wb(ytSnippetObj.items[i]);
}
if (typeof nextPageToken === "undefined") { break; }
}
function _wb(item){
var updateStatement = "UPDATE [dbo].[YouTubeVideos] set \r\n \
[ChannelId] = '<<ChannelId>>' \r\n \
,[Title] = N'<<Title>>' \r\n \
,[ChannelTitle] = N'<<ChannelTitle>>' \r\n \
,[Kind] = '<<Kind>>' \r\n \
,[VideoId] = '<<VideoId>>' \r\n \
,[Position] = '<<Position>>' \r\n \
,[PublishedAt] = '<<PublishedAt>>' \r\n \
,[RefreshedAt] = getdate() \r\n \
WHERE [Id] = '<<Id>>' and [PlayListId] = '<<PlayListId>>';";
var insertStatement = "\r\n IF @@ROWCOUNT=0 \r\n \
BEGIN \r\n \
insert into [dbo].[YouTubeVideos] (\r\n \
[Id], [ChannelId], [Title], [ChannelTitle], [PlayListId], [Kind], [VideoId], [Position], [PublishedAt], [IsEnabled], [RefreshedAt]) \r\n \
VALUES ('<<Id>>', '<<ChannelId>>', N'<<Title>>', N'<<ChannelTitle>>', '<<PlayListId>>', '<<Kind>>', '<<VideoId>>', '<<Position>>', '<<PublishedAt>>', 0, getdate()); \r\n \
END ";
var ups = updateStatement.replace('<<ChannelId>>', item.snippet.channelId);
ups = ups.replace('<<Title>>', item.snippet.title.replace(/'/g, "''"));
ups = ups.replace('<<ChannelTitle>>', item.snippet.channelTitle.replace(/'/g, "''"));
ups = ups.replace('<<PlayListId>>', item.snippet.playlistId);
ups = ups.replace('<<Kind>>', item.snippet.resourceId.kind);
ups = ups.replace('<<VideoId>>', item.snippet.resourceId.videoId);
ups = ups.replace('<<Position>>', item.snippet.position);
ups = ups.replace('<<PublishedAt>>', item.snippet.publishedAt);
ups = ups.replace('<<Id>>', item.id);
var ins = insertStatement.replace('<<Id>>', item.id);
ins = ins.replace('<<ChannelId>>', item.snippet.channelId);
ins = ins.replace('<<Title>>', item.snippet.title.replace(/'/g, "''"));
ins = ins.replace('<<ChannelTitle>>', item.snippet.channelTitle.replace(/'/g, "''"));
ins = ins.replace('<<PlayListId>>', item.snippet.playlistId);
ins = ins.replace('<<Kind>>', item.snippet.resourceId.kind);
ins = ins.replace('<<VideoId>>', item.snippet.resourceId.videoId);
ins = ins.replace('<<Position>>', item.snippet.position);
ins = ins.replace('<<PublishedAt>>', item.snippet.publishedAt);
var sqlStatement = ups + ins;
console.log(sqlStatement);
lib_task.LogMessage(sqlStatement);
lib_app.sql.Execute(lib_task.UserSessionIdGet(),'YOUTUBE', sqlStatement);
}
このタスクにトリガーを定義して、例えば毎週月曜日の朝5時に実行なんてすると便利かも。
データセット
上記のタスクが実行されると、このようなデータセットが出来上がります。そしてSharperlightパブリッシャー等を利用してWebレポートやオリジナルのVideoメニューなどを作成することができます。
次の記事では、このデータセットを利用したYouTubeビデオのオリジナルメニューの作成を紹介します。
あとがき
この例では毎週月曜日にデータセットが更新されます。その間に対象となるYouTubeチャンネルに新しいVideoが投稿されればその情報がデータベースに新たに保存されます。そうやって毎週データは最新のものに更新されるという手筈です。
こういった手法はその他のクラウドアプリにも使えそうですね。それらのAPIが充実していればの話ですが。
この記事が皆さんの業務のお役に立てれば光栄です。