LoginSignup
0
0

More than 1 year has passed since last update.

@a_b_

AlteryxでAthenaとBigQueryに接続

Last updated at Posted at 2022-10-16

はじめに

前回はAlteryxを使ってストレージに接続しました。

今回はAWSのAthenaと、GCPのBigQueryと接続を試してみました。

概要

試してみた結果は以下でした、BigQueryがどうにも芳しくなかったです。

  • Athena
    • 入力
      • 専用ツールで接続：ごく普通に取得可能
        • 中身はODBC経由
    • 出力
      • 専用ツール
        • Athenaのテーブルを都度作成
          • 既にテーブルがあった場合にエラーor再作成かは選択可
        • 全データ差し替え
  • BigQuery
    • 入力
      • 専用ツール：うまくいかず…
      • ODBC経由で接続可能
    • 出力
      • 専用ツール：うまくいかず…
      • ODBC経由も、うまくいかず…

Athena

以下のページを参考にしました。

Athena環境作成

まずはAthenaのテーブルを作成しますが、先に以下のものを作成します。

  • 対象バケット
  • 出力先バケット
  • ワークグループ
  • Glueデータベース

CFnで作成します。

クリックで表示
  • バケット名は、AWS管理のポリシーarn:aws:iam::aws:policy/AmazonAthenaFullAccessで使えるような接頭辞にしています。
    • データ格納バケット名:athena-examples-*
    • 結果格納バケット名:aws-athena-query-results-*
createCommonResource.yaml
AWSTemplateFormatVersion: '2010-09-09'

Parameters:
  Env:
    Description: Must not contain uppercase characters for Glue Database.
    Type: String
    AllowedPattern : "^[a-z0-9]*$"

Resources:
  RawDataBucket:
    Type: AWS::S3::Bucket
    Properties: 
      BucketName: !Sub athena-examples-${AWS::AccountId}-${AWS::Region}-${Env}
      BucketEncryption: 
          ServerSideEncryptionConfiguration: 
            - 
              ServerSideEncryptionByDefault: 
                  SSEAlgorithm: 'AES256'
              BucketKeyEnabled: false
      PublicAccessBlockConfiguration:
        BlockPublicAcls: true
        BlockPublicPolicy: true
        IgnorePublicAcls: true
        RestrictPublicBuckets: true

  AthenaQueryResultBucket:
    Type: AWS::S3::Bucket
    Properties: 
      BucketName: !Sub aws-athena-query-results-${AWS::AccountId}-${AWS::Region}-${Env}
      BucketEncryption: 
          ServerSideEncryptionConfiguration: 
            - 
              ServerSideEncryptionByDefault: 
                  SSEAlgorithm: 'AES256'
              BucketKeyEnabled: false
      PublicAccessBlockConfiguration:
        BlockPublicAcls: true
        BlockPublicPolicy: true
        IgnorePublicAcls: true
        RestrictPublicBuckets: true
      LifecycleConfiguration: 
        Rules: 
          - 
            Id: 'auto-delete'
            Status: 'Enabled'
            ExpirationInDays: 7

  AthenaWorkGroup:
    Type: AWS::Athena::WorkGroup
    Properties:
      Name: !Sub athena-work-group-${Env}
      RecursiveDeleteOption: true
      WorkGroupConfiguration:
        ResultConfiguration:
          OutputLocation: !Sub s3://${AthenaQueryResultBucket}/data
          EncryptionConfiguration: 
            EncryptionOption: 'SSE_S3'
        EnforceWorkGroupConfiguration: true
        PublishCloudWatchMetricsEnabled: true

  GlueDatabase:
    Type: AWS::Glue::Database
    Properties: 
      CatalogId: !Ref AWS::AccountId  
      DatabaseInput:
        Name: !Sub glue-database-${Env}

Outputs:
  RawDataBucket:
    Value: !Ref RawDataBucket
    Export:
      Name: !Sub "${Env}-RawDataBucket-Name"
  AthenaQueryResultBucket:
    Value: !Ref RawDataBucket
    Export:
      Name: !Sub "${Env}-AthenaQueryResultBucket-Name"
  AthenaWorkGroup:
    Value: !Ref AthenaWorkGroup
    Export:
      Name: !Sub "${Env}-AthenaWorkGroup-Name"
  GlueDatabase:
    Value: !Ref GlueDatabase
    Export:
      Name: !Sub "${Env}-GlueDatabase-Name"

次にAthenaのテーブルとユーザーを作ります。こちらもCFnで作りました。

クリックで表示
  • 入力だけであれば、ユーザーに宛がうポリシーはAmazonAthenaFullAccessだけでOK
  • パラメータEnvは、先ほどと同じ文字列を指定してください。
createAthena.yaml
AWSTemplateFormatVersion: '2010-09-09'

Parameters:
  Env:
    Type: String
  TableName:
    Type: String
    Description: Table name must not contain uppercase characters.
    AllowedPattern : "^[a-z0-9]*$"

Resources:
  GlueTable:
    Type: AWS::Glue::Table
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseName:
        Fn::ImportValue:
          !Sub "${Env}-GlueDatabase-Name"
      TableInput:
        Name: !Ref TableName
        TableType: EXTERNAL_TABLE
        Parameters:
          skip.header.line.count: 1
          has_encrypted_data: false
          serialization.encoding: utf-8
          EXTERNAL: true
        StorageDescriptor:
          OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
          Columns:
            - Name: "話数"
              Type: string
            - Name: "パート"
              Type: string
            - Name: "ポプ子"
              Type: string
            - Name: "ピピ美"
              Type: string    
          InputFormat: org.apache.hadoop.mapred.TextInputFormat
          Location:
            Fn::Join:
              - ''
              - - 's3://'
                - Fn::ImportValue: !Sub "${Env}-RawDataBucket-Name"
                - '/alteryx' 
          SerdeInfo:
            Parameters:
              field.delim: ","
              serialization.format: ","
            SerializationLibrary: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  AlteryxUser:
    Type: AWS::IAM::User
    Properties:
      ManagedPolicyArns:
       -  arn:aws:iam::aws:policy/AmazonAthenaFullAccess
      Policies:
       -  PolicyName: S3WriteForAthenaOut
          PolicyDocument:
            Version: 2012-10-17
            Statement:
             -  Effect: Allow
                Action: 
                 - s3:*Object
                Resource:
                 - 
                    Fn::Join:
                      - ''
                      - - 'arn:aws:s3:::'
                        - Fn::ImportValue: !Sub "${Env}-RawDataBucket-Name"
                 - 
                    Fn::Join:
                      - ''
                      - - 'arn:aws:s3:::'
                        - Fn::ImportValue: !Sub "${Env}-RawDataBucket-Name"
                        - '/*'

そして、バケットにデータを置きます。今回はalteryx/以下に置きました。

話数,パート,ポプ子,ピピ美
第1話,前半,平野綾,茅原実里
第1話,後半,井上和彦,堀川りょう

ユーザのアクセスキーを用いて接続しますので、先ほど作成したユーザのアクセスキーを用意します。

ODBC接続

以下のページの通りに行います。

image.png

うまくテストできれば、以下の様に接続確認できます。

image.png

入力

Alteryx上でデータ入力を置き、データソースから指定できます。

image.png

ODBCで設定したデータソース名を選び、アクセスキーとシークレットアクセスキーを入れます。
image.png

テーブルを指定する画面で、まずテーブルを選択します。
image.png
次に「SQLエディター」タブを開き、クエリをテストします。
image.png
データベース名にハイフンが入っているため、クエリが失敗しました。
そのため、ダブルクオーテーションで囲み、クエリが正常に実行されることを確認します。
image.png
OKを押したら、閲覧ツールをつなげて実行します。
image.png

出力

ここでの出力は、”DBに追加”ではなく、”テーブル再構築”という動きをする模様です。
以下のデータをもつAthenaのテーブルを作ります。カラム名は全角だとエラーになってしまいました。

episodes,part,popuko,pipimi
第1話,前半,平野綾,茅原実里
第1話,後半,井上和彦,堀川りょう
第2話,前半,朴璐美,釘宮理恵
第2話,後半,檜山修之,森川智之

データ出力ツールを置いて、データソースにAthena（バルク）を選択します。
image.png
プロパティには、以下を指定します。

  • アクセスキー
  • シークレットアクセスキー
  • エンドポイント
    • 今回はs3-ap-northeast-1.amazonaws.com
  • 対象バケットの名前（パスではないです）
    image.png

次の画面で新しく作られるテーブル名を指定します。
image.png

出力オプションを確認すると、以下の2つから選べます。
image.png
以下の動きのようです。

  • 新しいテーブルを作成する
    • 同名のテーブルがあった場合、エラー
  • テーブルを上書きする（ドロップ）
    • 同名のテーブルがあった場合、再作成
    • データファイルも差替えではなく、再アップ

新しいテーブルを作成する　の動き

まずは「新しいテーブルを作成する」で実行します。
指定したS3バケットに、新しくフォルダができ、gzipで固めたファイルが置かれています。
image.png
image.png

Athena側では、新しいテーブルができています。
image.png
検索すると、ちゃんと結果が返ってきます。
image.png

このまま再実行すると、以下の様にエラーになります。

Error: データ出力 (6): テーブル "hoshiiro2_cast" の作成中にエラー: [Simba][Athena] (1040) An error has been thrown from the AWS Athena client. Error Message: FAILED: ParseException line 1:8 missing EOF at '-' near 'glue' [Execution ID: 4c896356-fdc0-465b-8980-7a7cbada9b41] 
CREATE EXTERNAL TABLE hoshiiro2_cast (episodes VARCHAR(254),part VARCHAR(254),popuko VARCHAR(254),pipimi VARCHAR(254))ROW FORMAT DELIMITED
FIELDS TERMINATED BY ''LOCATION 's3://BUCKET_NAME/hoshiiro2_cast_4983cbe09cd74859beb1511c503fa896/'

ただ、データはS3にアップされています。
image.png

テーブルを上書きする（ドロップ）　の動き

次に「テーブルを上書きする（ドロップ）」で実行します。
先程作成されたテーブル、フォルダは削除しておきます。
出力オプションを変更して実行します。
image.png
「新しいテーブルを作成する」と同様、フォルダとデータがアップされ、テーブルが作成されます。
image.png
image.png
参照する場所は、Glue側で確認できます。
image.png

このまま再実行すると、新しくフォルダが作られ、参照先がそちらになります。
image.png
Selectの結果も問題なく取得できます。
image.png

データがまるまる置き換わることを確認するために、アップするデータを減らしてみます。
Alteryxのレコード選択ツールを用いて、3行目以降のデータのみ出力します。
image.png
参照先はさらに置き換わり、Selectの結果も減っていることが確認できます。
image.png
image.png
S3は、3回作り直したので、フォルダが3つ出来ています。
image.png

BigQuery(専用ツールを用いた方法)

上記でツールは用意されているのですが、以下のナレッジと同じことが起こってしまい、使用できませんでした。

今回は諦めました…

BigQuery(ODBCを用いた方法)

環境作成

BigQueryのテーブルを作成しておきます。
データセットを作成後、アップロードするCSVを基に作ってもらいます。
image.png
image.png

ODBC接続用のサービスアカウントも作成します。
image.png
image.png
鍵情報をjsonでダウンロードします。
image.png

ODBC接続設定については以下のページを参考にしました。先ほど作成したサービスアカウントを用いた方法になります。

サービスアカウントのファイルですが、ファイルパスに全角文字列が入っていると失敗します。
image.png

入力

以下のページを参考にしました。

データ入力ツールのデータソースに、BigQueryのODBCを設定します。
image.png
image.png
クエリエディタでSelect内容を指定します。
image.png

閲覧ツールをつなげて実行します。
image.png
問題なく取得できました。

出力

出力の方はうまくいきませんでした。
専用ツールは、入力と同様のエラーになり動きませんでした。
ODBC経由でも、以下のようなエラーになり、できませんでした。

Error: データ出力 (10): テーブル "sample20221013.cast" の作成中にエラー: [Simba][ODBC] (11210) Operation not allowed during the transaction state. 
CREATE  TABLE `sample20221013`.`cast` (`episodes` STRING,`part` STRING,`popuko` STRING,`pipimi` STRING)

おわりに

今回はAlteryxで、巨大データへのクエリサービスであるAthenaとBigQueryとの接続を試してみました。

出力に関して出来なかった点ありましたが、両サービスで扱うような巨大なデータを、Alteryx経由で送信するのはあまりよろしくないと思いますので、使い方がわかったところで利用はしないかな、と感じています。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0