【一般】YouTube APIを利用したソリューションの構築例

# はじめに #
以前YouTube APIを利用する記事をいくつか投稿しましたが、今回もまたそれに関連する記事です。
今回は、自分のYouTubeチャンネルの情報をローカルのデータベースに保存して、それを利用してレポートを作成してみようと思います。
YouTubeチャンネルからPlaylistの情報と各Playlistに属するVideoの情報を取得し、保存したいと思います。
前もってどのような情報が取得可能かをチェックし、以下のようなデータベースを準備しました。
Playlist用のテーブルとVideo用のテーブルが見えますね。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/bf8749b0-37e3-3eae-e184-472b2e97a074.png)
データを扱うなら[Sharperlight](https://sharperlight.jp/)っていうことで、早速Sharperlightでのソリューションの構築と参りましょう。
# ソリューション #
構築するソリューションを図にするとこんな感じになるのかな。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8a4d5e4f-a298-ec0c-7dd3-0272bb9f4c30.png)
## データモデル ##
データモデルの作成はこの[記事](https://qiita.com/Miki_Yokohata/items/73ffea777dd274c7da75)を参照してね。:grin:
で、このようなデータモデルが出来ました。
基本情報はこんな感じ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/3c14f0d5-4de0-3725-c99e-246f5956f283.png)
スキーマの定義はここ。JOINも追加してみました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a576f186-ef03-70ce-5e82-ceac51ddc466.png)
これでデータモデルの準備はOK。
## スケジュールタスク ##
ここではYouTube APIを利用してデータを取得する処理を、Sharperlightスケジュールタスクを利用して作成します。
YouTube APIを利用したタスクの作成はこの[記事](https://qiita.com/Miki_Yokohata/items/d0b85b43ae30e66b69c1)を参照してください。
そして、今回はそのタスクを編集して使用してみます。
処理の流れは、先ずPlailistの一覧を取得して、Playlist用のテーブルに保存。そのテーブルから保存されているPlaylistIDを取得して、そのID毎にループ処理を行いVideoの情報を取得しテーブルに保存していきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/30afc131-068c-5a1a-ffcb-03ec76fa85b8.png)
1. このアクションでは、Google API KeyやChannelIDなどのパラメータを準備しています。
2. 子タスクを呼び出しています。子タスクはYouTube APIからPlaylist一覧情報を取得して、テーブル[dbo].[YouTubePlayLists]に保存します。
3. Sharperlightクエリーを定義して、テーブル[dbo].[YouTubePlayLists]からPlaylist情報を読み取ります。またこのアクションはFor文の役目を果たします。クエリによって抽出されたデータを一行ずつ次のアクションに渡すことができます。
4. アクション3によって渡されるPlaylistIDを利用して、今度はYouTube APIからそのPlaylistに含まれるVideoの全情報を取得し、テーブル[dbo].[YouTubeVideos]に保存します。
5. このアクションは、単に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時に実行なんてすると便利かも。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e8ec17e2-3038-42a7-12f7-8cd3c4cfc6c6.png)
## データセット ##
上記のタスクが実行されると、このようなデータセットが出来上がります。そしてSharperlightパブリッシャー等を利用してWebレポートやオリジナルのVideoメニューなどを作成することができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e4f418ed-c70e-9a38-81ac-fda4f10b8541.png)
次の[記事](https://qiita.com/Miki_Yokohata/items/802a55d91ec3ed8d3f93)では、このデータセットを利用したYouTubeビデオのオリジナルメニューの作成を紹介します。
# あとがき #
この例では毎週月曜日にデータセットが更新されます。その間に対象となるYouTubeチャンネルに新しいVideoが投稿されればその情報がデータベースに新たに保存されます。そうやって毎週データは最新のものに更新されるという手筈です。
こういった手法はその他のクラウドアプリにも使えそうですね。それらのAPIが充実していればの話ですが。
この記事が皆さんの業務のお役に立てれば光栄です。