Amazon Kinesis/Redshift編～アクセスログをkinesisで加工してTableauで表示してみよう③ [全4回]～

1$ vim importS3toRedshift.php

1<?php23# パスは必要に応じて変更45include "/home/ubuntu/vendor/autoload.php";67use Aws\Redshift\RedshiftClient;89$client = RedshiftClient::factory(array(10    'key'    => 'ここにアクセスキー',11    'secret' => 'ここにアクセスキーシークレット',12    'region' => 'us-east-1'13));1415# redshiftの起動チェック（cron等で呼び出した時にredshiftが起動しているか確認）1617$result = $client->describeClusters(array());18$clusters = $result->get('Clusters');1920# 起動していなければredshftを起動させる2122if(count($clusters)==0){23    24# redshiftの起動2526    $res = $client->createCluster(array(27	'ClusterIdentifier'  => 'demoCluster',28	'ClusterType'        => 'single-node',29	'MasterUsername'     => 'dbmaster',30	'MasterUserPassword' => 'パスワード',31	'NodeType'           => 'dw2.large',32	'PubliclyAccessible '	=> true,33    ));34}else{35    echo "redshiftは起動しています。\n";36}3738# 起動していなければ10秒間隔でチェックし、redshiftのステイタスがtrueになるまで読み込み3940$ClusterStatus = "";41while($ClusterStatus!="available"){4243# cluster情報の取得4445    $result = $client->describeClusters(array(46	'ClusterIdentifier' => 'demoCluster',47    ));48    $clusters = $result->get('Clusters');4950# available, creating, deleting, rebooting, and resizing5152    $ClusterStatus = $clusters[0]['ClusterStatus'];53    $PrivateIPAddress = $clusters[0]['ClusterNodes'][0]['PublicIPAddress'];5455    if($ClusterStatus!="available") sleep(10);56}5758# redshiftに接続5960$conn = "host=".$PrivateIPAddress." port=5439 dbname=dev user=dbmaster password=Password1";61$link = pg_connect($conn);62if (!$link) {63    die('ERROR!'.pg_last_error());64}6566# エラーを取得するためのviewの作成6768$sql = "create view loadview as69(select distinct tbl, trim(name) as table_name, query, starttime,70trim(filename) as input, line_number, colname, err_code,71trim(err_reason) as reason72from stl_load_errors sl, stv_tbl_perm sp73where sl.tbl = sp.id);";74$result = pg_query($link, $sql);7576###################################################################################7778# ここは初回のみ実行(ただし、そのままでも問題なし)7980$sql = "create table accesslog (81         datetime timestamp,82         method varchar(12),83         path varchar(20),         84         protocol varchar(12),85         status int,86         ua varchar(20),87         ip varchar(15),88	 latitude real,89	 longitude real90);";91$result = pg_query($link, $sql);92$error = pg_last_error($link);9394# 1分前のログを送信9596$time = time() - 60;97$datetime = date("YmdHi", $time);9899# データ挿入100101$sql = "copy accesslog from 's3://kinesis-demo-knowcom/access_log.".$datetime."' CREDENTIALS 'aws_access_key_id=[ここにアクセスキー];aws_secret_access_key=[ここにアクセスキーシークレット]' delimiter '\t';";102$result = pg_query($link, $sql);103104$sql = "select datetime from accesslog";105$result = pg_query($link, $sql);106$rows = pg_num_rows($result);107echo date(DATE_RFC2822)."\t[redshiftのaccesslogデータに（".number_format($rows)."行）挿入完了]\n";108109$sql = "select * from loadview order by starttime desc ";110$result = pg_query($link, $sql);111while($row = pg_fetch_assoc($result)){112    var_dump($row);113}114$close_flag = pg_close($link);

1$ /usr/bin/php importS3toRedshift.php23redshiftは起動しています。4available       1404346396.9093秒56PHP Warning:  pg_query(): Query failed: ERROR:  relation "loadview" already exists in /home/ubuntu/redshift-kinesis.php on line 667PHP Warning:  pg_query(): Query failed: ERROR:  relation "accesslog" already exists in /home/ubuntu/redshift-kinesis.php on line 808Thu, 03 Jul 2014 00:13:17 +0000 [redshiftのaccesslogデータに（3,474行）挿入完了]