File Format

In previous chapters, we encountered the FILE FORMAT object , but we did not pay much attention to them. We used simple declarations to skip the first record, since this is typically a header record used to convey attribute names that already exist in our staging tables.

When writing this book, XML is only supported as a Public Preview feature.

Please also refer to Snowflake documentation at https://docs.snowflake.com/en/sql-reference/sql/create-file-format.html#create-file-format .

The order of precedence for a FILE FORMAT application varies according to where FILE FORMAT is declared. Please refer to https://docs.snowflake.com/en/user-guide/data-load-prepare.html#overriding-default-file-format-options .

JSON

JSON is short-hand for JavaScript Object Notation, a human-readable text format for storing and transporting information. JSON content is self-describing and, due to the nested nature of relationships in parent-child form, easy to understand.

Immediately we can read the JSON structure and recognize meaningful information, but what is less clear (this example has deliberately been structured to illustrate the point) is the built-in structured nature of JSON. To view in a more readable format, please navigate to https://jsonformatter.org , then type or cut and paste the preceding into the browser from the accompanying file, where a cleaner, easier-to-read version is presented in the right-hand pane.

Please experiment with changing the content and then “validate” your changes. Note the use of curly braces ({ }) that hold objects resulting in key/value pairs and square brackets ([ ]) that hold lists and arrays. These concepts are important to grasp for later use in materialized views explained next.

Variant Data Type

Now that you have a basic grasp of JSON , let’s prepare a file containing the preceding or reformatted content from https://jsonformatter.org and load it into S3. For this demonstration and using your favorite text editor, please create json_test.json and upload it into S3.

Another way to view JSON , assuming your browser opens JSON files, is to associate the file type with your browser when attempting to open json_test.json. You should see a stylized representation of the file content and tabs offering different views. In Firefox, these look like Figure 10-4. Note the partial screenshot shown.

With our sample file json_test.json uploaded to S3, we can see the json_test.json file contents.

For troubleshooting external stage issues, please see Chapter 7, where a step-by-step configuration guide is available.

For our semi-structured data , we use the VARIANT data type. There are others, including OBJECT and ARRAY. All are described at https://docs.snowflake.com/en/sql-reference/data-types-semistructured.html#semi-structured-data-types .

We should see our JSON data set as shown in Figure 10-5. when you click the record, the pop-up window displays nicely formatted JSON, as you saw in the right-hand pane at https://jsonformatter.org .

Handling Large Files

What if our JSON file exceeds the maximum variant size of 16 MB? What options do we have to load larger files?

But this approach does not work for JSON without an outer array to strip. We need to consider other options.

While researching this chapter, I came across a technique worthy of consideration. I have not tested or endorsed the next approach. I simply offer it as a novel way forward; see https://medium.com/snowflake/breaking-the-16-mb-limit-kinda-fe8d36658b .

Other approaches to splitting large JSON files include utilizing an ELT tool, which may offer built-in capabilities, or developing a custom process using a third-party tool to pre-process JSON into manageable chunks, as jq purports to do; see https://stedolan.github.io/jq/ . Also, consider using an external table to access your JSON file and pre-processing data before loading it into Snowflake.

Regardless of the approach chosen, remember the S3 bucket where your JSON file lands using SQS could invoke a Lambda function to pre-process, then copy to the S3 bucket where Snowpipe or other auto-ingestion processing occurs. Unfortunately, a fuller investigation is beyond the scope of this book.

Materialized Views

Having successfully landed our test file into our staging table, we must consider how to access the JSON content . At this point, we now discuss the merits of directly accessing JSON content and creating materialized views that overlay the JSON structure.

We aim to normalize the JSON structure into a relational format to facilitate joining with our structured data content. We may choose to implement a suite of three normal forms of materialized views which would extend the pattern explained here, which stops at a single denormalized materialized view.

To explain notation used in the code sample: The canonical reference to attribute tables then attribute:top level array[n].leaf node::datatype. Note the difference in the field delimiters. Figure 10-6 shows the output. As we expect, both attributes are in the same record.

Figure 10-7 shows the effect of using both LATERAL and FLATTEN . We can extend the attribute list using the same pattern to all attributes at the same level in the JSON record, which we do next.

Automation

In Chapter 8, we implemented Snowpipe , recognizing that materialized views by their nature are limited to the current version of data only. We may wish to historize ingested JSON documents.

However, we can do more with a stage directory where Snowflake supplies several file functions documented at https://docs.snowflake.com/en/sql-reference/functions-file.html#file-functions .

Note the task has not resumed because the stored procedure has not been declared; this is an exercise for you to complete.

File Preparation

In AWS Management Console , navigate to S3 and create a new S3 bucket: btsdc-ingest-bucket. We require a second bucket to prevent circular writes when the Lambda function is triggered.

For testing purposes, we use a simple PNG file created from a screenshot of an invoice.

We will later upload the test files into btsdc-ingest-bucket. The Lambda function is triggered, and files are copied to btsdc-test-bucket.

Lambda

We now focus on creating a Lambda function configured using our AWS account.

In AWS Management Console , search for “lambda” and select it, as shown in Figure 10-9.

Click “Create function” and ensure “Author from scratch” is selected. Populate the “Basic information” fields, as shown in Figure 10-10.

Click the “Create function ” button. You are presented with more configuration options, as shown in Figure 10-11. Note the Copy ARN, Add trigger and Upload from buttons.

A basic Lambda function is available in the Code source ➤ lambda_function tab. Note that the Deploy button is grayed out. Whenever we change the Python code, the Deploy button is enabled.

Click “Add trigger” and populate S3. Select your ingestion bucket name, and the “All object create events” event type. Select the “Recursive invocation” checkbox and click Add. See Figure 10-12.

If an error is received during your testing, it may be due to prior event registration. Navigate to S3 ➤ Properties ➤ Event notification and remove any events from file_to_json, and then retry.

Triggers can be viewed under the Configuration ➤ Triggers tab.

The execution role can be viewed under the Configuration ➤ Execution role tab, which needs amending. Click Edit and select the “Create a new role from AWS policy templates” option. Enter a name: S3-ingest-to-S3-stage. Then select “Amazon S3 object read-only permissions” from the drop-down list. The screen automatically returns to the Lambda summary screen.

This URL navigates directly to the page to edit; see https://console.aws.amazon.com/iamv2/home?#/roles/details/S3-ingest-to-S3-stage?section=permissions .

Alternatively, navigate to Lambda ➤ Functions ➤ file_to_json ➤ Configuration ➤ Execution role.

Click “Review policy” and then the “Save changes” button, which returns to the Lambda function overview.

This code copies a file from the source S3 bucket into the destination bucket. Cut and paste it into the Code tab to test if the policy works correctly.

The following code is intended to be cut and pasted, Python is indenting sensitive, and the code format incorrectly implies a line break after the “# Specify source bucket” and “# Write copy statement” statements.

Then click Deploy.

Upload files to btsdc-ingest-bucket and check they appear in btsdc-test-bucket. We may also check the file has been copied across correctly by downloading it from the target S3 bucket.

Delete files from both btsdc-ingest-bucket and btsdc-test-bucket.

Extracting File Content

This section has the potential to expand into a separate book itself. One challenge with authoring code samples is keeping content to manageable sections while conveying enough information to inform and equip the audience. Cue one epic fail!

Let’s start by investigating Amazon Textract, a file processing utility. Its documentation is at https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/textract.html#Textract.Client.analyze_document . Note that Textract has two modes of operation: synchronous for small files and asynchronous for larger files. In this chapter, we investigate Synchronous operation using textract.analyze_document. Documentation is at https://docs.aws.amazon.com/textract/latest/dg/analyzing-document-text.html while integrating with our S3 copy code.

I confess. I did not author all of the following code. It is based upon code supplied and used with the kind permission of Saurin Shah of Snowflake Inc., extended to showcase the “art of the possible” with apologies for removing comments and blank lines to reduce the page count. The accompanying file has a complete version.

Mapping to JSON

There is one caveat. During testing, we encountered the following error.

Invalid UTF8 detected in string ‘ “0xA3 ”: “”,’ File ‘test_json.png.json’, line 38, character 1, row 37, column “TRANSIENT_STAGE_TABLE”[“$1”:1].

The fix was to delete the offending line (38) in JSON and then reload and retry.

You may also find it necessary to load your file into https://jsonformatter.org and save the output before uploading .

With data accessible, I leave it to you for further processing.

Troubleshooting

If files have not been copied across, navigate to Lambda ➤ Functions ➤ file_to_json ➤ Monitor ➤ Logs. Click the “View logs in CloudWatch” option, which opens a new browser tab showing all available log streams in date order, the most recent first. Refresh, and then click the most recent log where information similar to what is shown in Figure 10-13 is displayed. Note the example shown is for two Lambda invocations, the first failed, and the second most recent invocation succeeded.

Further information can be found by opening up each section where the Lambda error line and reason appear, as shown in Figure 10-14.

Tracking Template

We populate the following template copied from https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws-planning.html#preparing-to-use-the-aws-management-console . You may also wish to add this template to your support documentation for each external function created.

Create a Python Lambda Function

Repeating the steps from Figure 10-9 and Figure 10-10, create a new Lambda function called parse_image. Select Python 3.9 as the runtime, then leave the default execution role as “Create a new role with basic Lambda permissions” before clicking the “Create function” button. After a minute or so, our new function appears similar to Figure 10-11.

You should see “Execution result: succeeded.”

Create AWS IAM Role

We now use AWS Management Console to configure the IAM role for Snowflake use. Click Roles ➤ Create role. Our role is for another AWS account which is a trusted entity. Select “AWS account” and use your account number recorded in the tracking template (mine is 616701129608). Then click Next ➤ Next. Add a meaningful name such as Snowflake_External_Function, and then click Create role.

Once created, click the new role and record the ARN. (Mine is arn:aws:iam::616701129608:role/Snowflake_External_Function.)

Get Snowflake VPC Information

From which we add privatelink-vpce-id: com.amazonaws.vpce.eu-west-2.vpce-svc-0839061a5300e5ac1 to our tracking template. Note that yours will differ.

Create API Gateway Endpoint

In AWS Management Console , search for “API Gateway ” and select the service. From the options presented, find Rest API and click the Build button. Then select New API, at which point you see the Settings screen.

Enter the API name: get_image_content. Optionally, you can populate the Description field. Leave the Endpoint Type set to Regional, and then click Create API.

In the Methods screen, click Actions and select Create Resource. Set the resource name to snowflake_proxy before clicking the “Create resource” button.

Next, click Actions and select Create Method. From the drop-down list, select POST, and click the tick mark. Leave Lambda Function selected as the integration type. Select the “Use Lambda Proxy integration” option. In the Lambda Function dialog box, enter parse_image and click Save. In the pop-up box, click OK. The screen now presents a schematic showing request/response. This can be ignored.

From the Actions drop-down, select Deploy API action. Set the Deployment Stage to [New Stage], set the stage name to “test”, and then click Deploy.

The newly created “test” stage can now be selected and the tree opens. Click POST and record the Invoke URL (in my example, https://8qdfb0w8fh.execute-api.eu-west-2.amazonaws.com/test/snowflake_proxy ). Record this in the tracking template. Note that yours will differ.

Secure API Gateway Proxy

Click Amazon API Gateway ➤ get_image_content ➤ POST ➤ Method Request. Next, click the Edit symbol in Authorization , and in the drop-down list, select AWS IAM. Click the tick mark to save. From the Actions drop-down list, select the Deploy API action. Set the deployment stage to “test” and click Deploy.

Then click Method Execution and retrieve the ARN; in my example, it is arn:aws:execute-api:eu-west-2:616701129608:8qdfb0w8fh/*/POST/snowflake_proxy. Record this in the tracking template. Note thatyours will differ.

On the left of the screen, select Resource Policy and paste the example resource policy from the documentation at https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws-ui-proxy-service.html#secure-your-amazon-api-gateway-endpoint .

Replace <12-digit-number> with your AWS account ID, in our example this is 616701129608. Replace <external_function_role> with Snowflake_External_Function. Then replace <method_request_ARN> with your method execution ARN.

Click the Save button before clicking get_image_content in the header bar. Click Actions ➤ Deploy API. In the deployment stage, select “test” and then click Deploy.

Snowflake API Integration

In this section, I have substituted values from my AWS account; yours will differ and must be replaced.

Establish Trust Relationship

Then click the “Update policy” button. If we later update the API integration, the trust relationship will also need updating.

Create Snowflake External Function

Calling External Function

The successful response is to echo the input parameters, as shown in Figure 10-16.

Deploy Lambda Image

This section modifies parse_image to scrape content from an image file.

Assign IAM Policy

Click the “Review policy” button and then save the changes. You may be prompted to remove an old version of test_policy.

Next, attach policy. Navigate to IAM ➤ Roles ➤ Snowflake_External_Function ➤ Add permissions ➤ Attach policies. You may also find a default role created for parse_image called something like parse_image-role-eu6asv26 to which test_policy should be attached.

Set the checkbox for test_policy and then click the “Attach policies” button. Do not forget to check that the policy has been attached. Navigate to IAM ➤ Roles ➤ Snowflake_External_Function and check that test_policy appears under the “Permissions policies” section.

Invoke Lambda Image

There is a limitation with get_presigned_url function insofar as this must be a fully qualified literal stage name declared as an object and not a quoted string passed through. For more information, see the documentation at https://docs.snowflake.com/en/sql-reference/functions/get_presigned_url.html#get-presigned-url .

Figure 10-17 shows expected results, yours will differ, and the address has been deliberately blanked out.

The JSON document returns fields and values, but due to the way in which Textract operates, not every key/value pair is returned. A full explanation is beyond the scope of this chapter, but briefly, text in images is bound by boxes before extract, and it is the content of each box that is returned. Examine the returned JSON for information. Note thattrailing spaces are significant for matching specific fields.

Automation

Our next step is to automate document JSON extract and parsing, an exercise left for you to complete using your knowledge of streams and tasks.

Monitoring

In AWS Management Console , navigate to CloudWatch ➤ Log groups, where you see the /aws/lambda/parse_image log group. Click it to show the latest logs.

Note that upstream errors (i.e., those which do not cause Lambda to be invoked) are not recorded. Only Lambda invocations create log entries that may not be successful.

Troubleshooting

Inevitably there will be times when we need to fix our code, and during the creation of this example, I made several, hence my note to use the tracking template . One “gotcha” stands out. In the Method Request” setting, I failed to redeploy Actions ➤ Deploy API. I easily missed it. After which, I encountered a further error relating to the API key. See https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws-troubleshooting.html#request-failed-for-external-function-ext-func-with-remote-service-error-403-message-forbidden to which the answer was to disable the API key from the Method Request.

The last error encountered related to Lambda not having an entitlement to access Textract, specifically analyze_document, as identified in the CloudWatch logs. The solution was to add a policy containing appropriate entitlement to the Lambda function.

The following document offers solutions to common issues: https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws-troubleshooting.html#troubleshooting-external-functions-for-aws .

And for AWS-specific issues, see https://docs.snowflake.com/en/sql-reference/external-functions-creating-aws-troubleshooting.html#label-external-functions-creating-aws-troubleshooting .