2012年12月23日 (日)

VPCにハマった日

 先日、awsのVPCの仕様でハマった事があったので書いておきたいと思います。

それは、EC2インスタンスをVPC上のサブネットで起動してEIPをつけてSSHでログインしようとした時の事なのですが、どうしてもそのEC2インスタンスに接続できませんでした。

最初はセキュリティーグループの設定をすればOKだろうとタカをくくっていたのですが、2時間近くいろいろと試したあげく、どうしても自分では解決できずawsのサポートにサポートを依頼しました。

するとこんな回答が。。。

”このEC2インスタンスの接続されているサブネットからインターネットゲートウェイ(igw)に向けてルーティングが設定されていないようです。”

そうです、EIPを付けると無条件にインターネット側からアクセスできるように思い込んでいたのですがVPCの場合igwを設定していないサブネット上のEC2にはインターネット側からは接続できないのです。

では、なぜこのサブネットにはigwが設定されていないのかというと、実はこのサブネットはダイレクトコネクトを使って会社のLANに接続されているのでそちらに向けてしかルーティングが設定されておらず、このインスタンスにログインするには、他のigwの設定されているサブネット上のインスタンスを経由する必要があったのでした。

今回はしょうもない事で2時間近くも無駄にしてしまいましたが、勉強になりました。

もう少しネットワークの事を勉強しなければと思った日でした。

2012年12月11日 (火)

OracleでPivot

仕事で大量のデータをPivot集計する必要がでてきたので、Oracle11gで追加されたPivot関数を使ってみました。 11g以前でも、集計関数とdecode,case関数を駆使すればPivot的なことはできましたが、11gでは内部的に同じようなことをやってくれるようになったようです。

それでは下の様にtab1テーブルを用意して適当にデータを投入しておきます。

SQL> desc tab1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DT                                        NOT NULL DATE
 ITEM_NAME                                          VARCHAR2(1)
 NUM                                                VARCHAR2(4)


SQL> select * from tab1 where rownum<10 order by dt;

DT                  I NUM
------------------- - ----
2012/12/06 18:50:52 C 2
2012/12/06 18:50:53 C 3
2012/12/06 18:50:53 C 3
2012/12/06 18:50:56 C 6
2012/12/06 18:57:54 c 4
2012/12/06 18:58:13 c 3
2012/12/06 18:58:21 c 1
2012/12/06 18:58:52 C 2
2012/12/06 19:03:22 A 2

9 rows selected.

SQL> select count(*) from tab1;

  COUNT(*)
----------
     30638

SQL>

シチュエーションとしては”インターネットの時間限定販売でA,B,Cという商品を販売して、1時間毎の販売数を集計”といった所でしょうか。

いままでは

SQL> select
  2  to_char(dt,'yyyy/mm/dd hh24'),
  3  sum(decode(item_name,'A',num,null)) A,
  4  sum(decode(item_name,'B',num,null)) B,
  5  sum(decode(item_name,'C',num,null)) C
  6  from tab1
  7  group by to_char(dt,'yyyy/mm/dd hh24')
  8  order by 1
  9  /

TO_CHAR(DT,'Y          A          B          C
------------- ---------- ---------- ----------
2012/12/06 18         53                   916
2012/12/06 19       9843        277       3200
2012/12/06 20      23510        631       1720
2012/12/06 21      11135        525        867
2012/12/06 22         95                   328

SQL> 

などとやっていましたが、Pivot関数を使用すると

SQL>  select * from (
  2  select to_char(dt,'yyyy/mm/dd hh24'),item_name,num from tab1)
  3  pivot (sum(num) for item_name in ('A','B','C'))
  4  order by dt
  5  /

DT                   'A'        'B'        'C'
------------- ---------- ---------- ----------
2012/12/06 18         53                   916
2012/12/06 19       9843        277       3200
2012/12/06 20      23510        631       1720
2012/12/06 21      11135        525        867
2012/12/06 22         95                   328

SQL>  

このようにかなりシンプルなクエリになりました。
集計するアイテム数が増えるほど、Pivot関数のありがたみが増しますね。

無料ブログはココログ