AlteryxでAthenaとBigQueryに接続

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

https://qiita.com/a_b_/items/4c58e6bf7f4761273b36

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

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

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


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

https://dev.classmethod.jp/articles/alteryx-odbc-athena/

## Athena環境作成
まずはAthenaのテーブルを作成しますが、先に以下のものを作成します。
- 対象バケット
- 出力先バケット
- ワークグループ
- Glueデータベース

CFnで作成します。

<details><summary>クリックで表示</summary><div>

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

```yaml: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"
```
</div></details>

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

<details><summary>クリックで表示</summary><div>

- 入力だけであれば、ユーザーに宛がうポリシーは`AmazonAthenaFullAccess`だけでOK
- パラメータ`Env`は、先ほどと同じ文字列を指定してください。

```yaml: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"
                        - '/*'
```

</div></details>

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

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

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


## ODBC接続
以下のページの通りに行います。

https://dev.classmethod.jp/articles/aws-athena-odbc/

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/460faaac-0c26-3f0f-6251-82d59d0ad019.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/5a8f18c8-808e-22a1-3a74-65cef046952f.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/512d4428-565b-c763-7e87-8481be413c0b.png)

ODBCで設定したデータソース名を選び、アクセスキーとシークレットアクセスキーを入れます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/91e1eaad-0bf9-f4e0-b55e-f8a8d11d8232.png)

テーブルを指定する画面で、まずテーブルを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/cebcb99d-1d5b-378a-5372-f38c7c9e5ff1.png)
次に「SQLエディター」タブを開き、クエリをテストします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/f27a98d5-c46e-67ae-c198-fa32eea4909e.png)
データベース名にハイフンが入っているため、クエリが失敗しました。
そのため、ダブルクオーテーションで囲み、クエリが正常に実行されることを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/14e234f0-d75b-9455-006d-f92c6c6d00c2.png)
OKを押したら、閲覧ツールをつなげて実行します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/1957beda-a02f-d1d4-49ea-d2283ad553d2.png)


## 出力
ここでの出力は、”DBに追加”ではなく、”テーブル再構築”という動きをする模様です。
以下のデータをもつAthenaのテーブルを作ります。カラム名は全角だとエラーになってしまいました。
```csv
episodes,part,popuko,pipimi
第1話,前半,平野綾,茅原実里
第1話,後半,井上和彦,堀川りょう
第2話,前半,朴璐美,釘宮理恵
第2話,後半,檜山修之,森川智之
```

データ出力ツールを置いて、データソースにAthena（バルク）を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/c6425116-79fd-d654-640d-a61d5ef3badf.png)
プロパティには、以下を指定します。
- アクセスキー
- シークレットアクセスキー
- エンドポイント
    - 今回は`s3-ap-northeast-1.amazonaws.com`
- 対象バケットの名前（パスではないです）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/bc4e11c2-819c-e8be-64cb-d7525fdb8e50.png)

次の画面で新しく作られるテーブル名を指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/1852cd41-b4e0-2c91-9a0f-95ba690639ef.png)

出力オプションを確認すると、以下の2つから選べます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/60a572d3-98ae-3250-b658-d8c3af9ed6d1.png)
以下の動きのようです。
- 新しいテーブルを作成する
    - 同名のテーブルがあった場合、エラー
- テーブルを上書きする（ドロップ）
    - 同名のテーブルがあった場合、再作成
    - データファイルも差替えではなく、再アップ

### 新しいテーブルを作成する　の動き
まずは「新しいテーブルを作成する」で実行します。
指定したS3バケットに、新しくフォルダができ、gzipで固めたファイルが置かれています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/a6d90718-31d7-7a71-25de-420dc831d7eb.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/96a0e731-bd9f-020d-87cb-f3e52069a8a8.png)

Athena側では、新しいテーブルができています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/85f80f27-b6c1-ff86-9362-661570b6c871.png)
検索すると、ちゃんと結果が返ってきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/94bfee01-9b82-074a-6d02-d30294d4b539.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/fdafa353-0c7d-65e3-2ee7-3e85c0264917.png)

### テーブルを上書きする（ドロップ）　の動き
次に「テーブルを上書きする（ドロップ）」で実行します。
先程作成されたテーブル、フォルダは削除しておきます。
出力オプションを変更して実行します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/ae794533-464c-871f-b020-5f2689555b15.png)
「新しいテーブルを作成する」と同様、フォルダとデータがアップされ、テーブルが作成されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/3435f485-d083-89ec-8adb-675362f58e07.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/ea21de6f-b4d2-93bb-ad15-f37663416f73.png)
参照する場所は、Glue側で確認できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/822e46ec-04ef-a969-8dfa-cd625fdcc369.png)

このまま再実行すると、新しくフォルダが作られ、参照先がそちらになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/880953f0-b62b-9ff0-3bf9-ed002022d58f.png)
Selectの結果も問題なく取得できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/0f800857-2f90-3ce2-2c6a-fd8a03d2894d.png)

データがまるまる置き換わることを確認するために、アップするデータを減らしてみます。
Alteryxのレコード選択ツールを用いて、3行目以降のデータのみ出力します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/7b72d830-2c7d-ef24-2ba3-88b7e16361d7.png)
参照先はさらに置き換わり、Selectの結果も減っていることが確認できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/6c343e5f-9c05-b3ab-7817-14770920f534.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/c6612470-4287-1828-0b92-0daa51d4959c.png)
S3は、3回作り直したので、フォルダが3つ出来ています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/a5edad8b-49f1-604d-4775-dfb91b4166dc.png)

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

https://community.alteryx.com/t5/Public-Community-Gallery/Google-BigQuery-Tools/ta-p/877885

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

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/GDC-3086-Google-Big-Query-Error-AttributeError-NoneType-object/ta-p/1008993

今回は諦めました…

# BigQuery(ODBCを用いた方法)

## 環境作成

BigQueryのテーブルを作成しておきます。
データセットを作成後、アップロードするCSVを基に作ってもらいます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/74fd7ff1-5d63-2962-08e0-bc64605b0a12.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/cd1a68aa-b111-d975-d9d8-04add9b6df67.png)

ODBC接続用のサービスアカウントも作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/82323fe0-0929-1586-0fcc-0927bcb8a5fb.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/1de4df6d-b545-d019-34f9-3d31d58fb0a6.png)
鍵情報をjsonでダウンロードします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/22e686d3-ec83-fbba-7485-8d8fd761015b.png)


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

https://dev.classmethod.jp/articles/google-bigquery-magnitude-simba-odbc-driver/

サービスアカウントのファイルですが、ファイルパスに全角文字列が入っていると失敗します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/0894d1e3-de6f-81fc-9125-ca4c1dd35fbd.png)

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

https://dev.classmethod.jp/articles/how-to-extract-data-from-google-bigquery-using-alteryx-designer/

データ入力ツールのデータソースに、BigQueryのODBCを設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/ed2d7b14-9346-8089-6f58-ebf0383a32a9.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/74a7eed1-6564-6f6f-a2da-ce24f4e9dc10.png)
クエリエディタでSelect内容を指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/d10ed59d-ad06-f84a-c65b-e108ed8642f7.png)

閲覧ツールをつなげて実行します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/05be117e-9f9b-061a-7ae8-1bd2a50e798a.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経由で送信するのはあまりよろしくないと思いますので、使い方がわかったところで利用はしないかな、と感じています。