AWS Data Lakehouse

GRAX Data Lake automatically organizes your CRM data as Parquet files in S3 for arbitrary on-demand analytics queries.

GRAX Data Lakehouse uses AWS Glue to catalog your data lake data, and AWS Athena to query it by SQL queries that run on the Athena in a serverless and scalable fashion.

CloudFormation Quick Deploy

Use CloudFormation Quick Deploy to set up a Lakehouse.

You will need to switch to the correct region that your GRAX deployment and data is in.

By default the stack creates a new S3 Bucket, Glue Data Catalog and Athena Database all configured properly for storing and querying data.

Connect the S3 Bucket to GRAX with the provide Role ARN.

Existing Bucket

If you already have a bucket with GRAX data, fill in S3BucketName .

The template will configure Glue and Athena to use the existing bucket and data.

After you create the stack you will need to configure your bucket to send event notifications to the SQS queue in SQSQueueArn output. If your org ID is 00D46000001EXAMPLE, use parquet/v2/ORG%3D00D46000001EXAMPLE as your notification prefix filter to avoid issues with the = special character.

Using Amazon SQS, Amazon SNS, and Lambda - Amazon Simple Storage ServiceAmazon Simple Storage Service

Enabling and configuring event notifications using the Amazon S3 console - Amazon Simple Storage ServiceAmazon Simple Storage Service

IAM Access Keys

To connect the lakehouse to an external system like Metabase or your laptop, set S3AccessMethod to User . The IAM key and secret is available in Secrets Manager.

Query

Next configure an environment with your data lake credentials, then list what objects are in your data lake.

# configure AWS credential chain
export AWS_ACCESS_KEY_ID=AKIA4WWOSMD6PEXAMPLE
export AWS_SECRET_ACCESS_KEY=<REDACTED>
export AWS_REGION=us-east-1
export BUCKET=my-grax-bucket
export ORG=00D46000001EXAMPLE

Finally we can query our data lake with aws athena. First we count how many Account versions we have and select one record:

QUERY="SELECT COUNT(*) FROM object_account"

aws athena start-query-execution \
   --query-string $QUERY \
   --query-execution-context Database=default \
   --result-configuration OutputLocation=s3://$BUCKET/athena-results \
   --output text

654059a8-8455-4ecf-b539-3a694847aa15

aws athena get-query-results --query-execution-id 654059a8-8455-4ecf-b539-3a694847aa15

{
  "ResultSet": {
    "Rows": [
      {
        "Data": [
          {
            "VarCharValue": "_col0"
          }
        ]
      },
      {
        "Data": [
          {
            "VarCharValue": "126178"
          }
        ]
      }
    ]
  }
}

QUERY="SELECT Id, Name FROM object_account LIMIT 1"

aws athena start-query-execution \
   --query-string $QUERY \
   --query-execution-context Database=default \
   --result-configuration OutputLocation=s3://$BUCKET/athena-results \
   --output text

f7717a2a-19ef-4b81-9d1a-858abb847a6a

aws athena get-query-results --query-execution-id f7717a2a-19ef-4b81-9d1a-858abb847a6a

{
  "ResultSet": {
    "Rows": [
      {
        "Data": [
          {
            "VarCharValue": "Id"
          },
          {
            "VarCharValue": "Name"
          }
        ]
      },
      {
        "Data": [
          {
            "VarCharValue": "0014600000zEXAMPLE"
          },
          {
            "VarCharValue": "Example Acct"
          }
        ]
      }
    ]
  }
}

Views

Your GRAX data lake has rows for every version of every record. However many analytics questions start by only looking at the most current "live" data. Here we create a view that reads all versions but returns just the latest "live" data.

QUERY="CREATE OR REPLACE VIEW object_account_live AS
WITH max_idseq AS (
  SELECT id AS mid, MAX(grax__idseq) AS max_idseq
  FROM object_account
  GROUP BY 1
),
live AS (
  SELECT *
  FROM object_account o
  JOIN max_idseq m ON m.mid = o.id
  AND grax__idseq = max_idseq
  AND grax__deleted IS NULL
)
SELECT * FROM live
"

aws athena start-query-execution \
   --query-string $QUERY \
   --query-execution-context Database=default \
   --result-configuration OutputLocation=s3://$BUCKET/athena-results \
   --output text

Now we can query the live data easily:

QUERY="SELECT COUNT(*) FROM object_account_live"

aws athena start-query-execution \
   --query-string $QUERY \
   --query-execution-context Database=default \
   --result-configuration OutputLocation=s3://$BUCKET/athena-results \
   --output text

e8a35172-27c2-418b-911b-7cd470837797

aws athena get-query-results --query-execution-id e8a35172-27c2-418b-911b-7cd470837797

{
  "ResultSet": {
    "Rows": [
      {
        "Data": [
          {
            "VarCharValue": "_col0"
          }
        ]
      },
      {
        "Data": [
          {
            "VarCharValue": "23157"
          }
        ]
      }
    ]
  }
}

Resetting Your Data Lake

First disable all objects in GRAX Data Lake. This stops new data from writing.

Next, clear out the Parquet data from S3:

aws s3 rm --recursive s3://$BUCKET/parquet/v2/org=00D46000001EXAMPLE/

Next delete all objects in GRAX Data Lake. This will reset objects back to the beginning of time.

Finally, re-enable all objects in GRAX Data Lake. This will rewrite all data from the beginning of time.