Explained in #39 (comment)

The config option is accessed by the target but isnt advertised in the target.py config options. Also I was getting an error saying that it was required before the working group PR was implemented so its relatively critical.

There are currently three different config fields related to s3 data location keys:

• s3_key_prefix - Used in defining target_key for s3 upload, and used in data_locatioin for athena table definition.
• naming_convention - Used only for defining target_key for s3 upload.
• s3_staging_dir - Not being used in the sink function, but apparently still set as 'required' somewhere. (will fail CLI if not set)

We can probably consolidate these into at least one config option.

Currently uses add_metadata_columns to add metadata columns to records. This is incorrect. The SDK uses add_record_metadata. Setting add_metadata_columns to true does not add the metadata columns.

One thing we've been thinking about with the SDK is how much effort it would be to have 'ambidextrous' targets, meaning targets that could also be taps - aka, on the 'left' or 'right' side of the Singer pipeline. Since I haven't seen a tap-athena, I decided to start exploring this in a new branch: #1

Hello people, I'm starting to use this target and I'm missing some features that I'm already working to make some contributions here, but I think that we can make this codebase more simpler using AWS Data Wrangler instead of pyathena.

IDK if anyone here has worked before with this library, but aws data wr abstracts all the AWS calls and catalog/database manipulation and data upload to s3 making easier to implement the parquet writer #9 for example.

Can we discuss about?

References:
https://aws-data-wrangler.readthedocs.io/en/stable/tutorials/006%20-%20Amazon%20Athena.html
https://aws-data-wrangler.readthedocs.io/en/stable/tutorials/005%20-%20Glue%20Catalog.html
https://aws-data-wrangler.readthedocs.io/en/stable/tutorials/003%20-%20Amazon%20S3.html
https://aws-data-wrangler.readthedocs.io/en/stable/tutorials/012%20-%20CSV%20Crawler.html
https://aws-data-wrangler.readthedocs.io/en/stable/tutorials/017%20-%20Partition%20Projection.html

Testing #12 with the transferwise variant of tap-mongodb, it looks like every single schema generated by the target only contains _id,document, and _sdc_deleted_at. I'm not exactly sure what's going on here, but I'm guessing we'll have to by default flatten self.schema or something?

• tap: https://hub.meltano.com/taps/mongodb--transferwise
• target: #12

CREATE EXTERNAL TABLE `patterns`(
  `_id` string COMMENT 'from deserializer', 
  `document` string COMMENT 'from deserializer', 
  `_sdc_deleted_at` string COMMENT 'from deserializer')
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION
  '-'

Use case: user may want to select alternative serde.

• org.apache.hive.hcatalog.data.JsonSerDe
• org.openx.data.jsonserde.JsonSerDe

https://docs.aws.amazon.com/athena/latest/ug/creating-databases.html

Open question here. Normally db creation seems to be handled outside of targets. It could make sense for this target to just create a db though.

In theory, because we register data in the glue catalog, those data sources might be already available for redshift spectrum tables. We would need to test+confirm this to see if this connector can be used more broadly for Redshift Spectrum as well as natively in Athena.

From @aaronsteers

One other bug I think I found, which I didn’t document, was that I wasn’t sure if the compression was getting handed off to Athena properly. I think my first attempt with compression made the target table unreadable. I just turned it off without much further testing, but if we want to do jsonl/json, the compression would probably come up there also.

I'm using tap-google-analytics which returns record keys using camelCase. During table creation the fields are automatically converted to lowercase by Athena so then when I run a query its looking for keys using the lower case variant and ultimately returns null for the entire column.

I was able to get it to work by rerunning the create external table statement with case.insensitive=true in the DDL I pulled from the logs which included:

WITH SERDEPROPERTIES (
  'ignore.malformed.json'='true',
  'case.insensitive'='false'
  ) 

We set case.insensitive=false in the target when using jsonl records.

Proposed Solution:

1. set case.insensitive=true by default for jsonl. I don't see any harm in doing this but there might be an edge case I'm missing. If there are records with the same name that are distinct by casing theyll get clobbered either during table creation or on read but either way there will be a problem.
2. As part of writing to jsonl files we could lowercase all field names in the record and potentially do any additional transformations needed for Athena to accept it. Other transformations could include replacing special characters or aliasing reserved column names.

I think option 1 is best to solve the short term problem and although I'm sensitive to transforming records in the target I think option 2 might be the long term solve in addition to capture other edge cases or at least send them through a filter and send a warning log or something.

When the input stream is DB, Meltano's naming convention for entity and attributes commonly has the form <DB name>-<table name>.<column name>, which includes a hyphen. The current implementation tries to create an Athena table with this naming convention, but hyphens are not allowed in the Athena table.

https://docs.aws.amazon.com/athena/latest/ug/glue-best-practices.html#schema-names

The only acceptable characters for database names, table names, and column names are lowercase letters, numbers, and the underscore character.

A quick fix may be converting hyphens to underscores when creating the DDL. What do you think?

This function was lifted from an example as a first pass at inferring the schema for generating the Hive table definition. Unfortunately it is fairly buggy and a workaround currently renders every field as a string.

Hive table field definitions should conform to https://docs.aws.amazon.com/athena/latest/ug/data-types.html

In Athena parlance, catalog is basically analogous to database and database is analogous to schema. We are currently creating the "schema" if it doesn't exist, but should we also potentially create a catalog?

I have been trying to run a tap-shopify to target-athena pipeline. However, when checking created tables in Athena, some cells are misaligned.

The same issue happens in target-csv. The data is heavily nested and includes arrays of JSON. e.g., If a customer purchases 1 item vs. five items, then line_items (and other columns) will grow to fit information on each.

Currently a stream's schema is transmitted through a Sink object as a regular Python dict - which may or may not be ordered, depending on python version (apparently?)

Anyway, we should determine whether or and how column order should be preserved both in the Hive table definition and in the written file objects (for json and csv at least).

Parquet is likely the preferred format to use in many Athena applications.

Examples

• https://github.com/mirelagrigoras/target-parquet
• https://github.com/transferwise/pipelinewise-target-snowflake

Test description. Test test test.

Incremental CSV writing logic may not be able to anticipate variations in tap schema as can be common with instances like tap-mongodb.

Currently tables are created with CREATE EXTERNAL TABLE IF NOT EXISTS {database}.{table}, but one may want to replace or update an existing table definition.

We're running this target for the hub and are seeing slow performance. I believe the issue is described here and may be improved by https://gitlab.com/meltano/sdk/-/merge_requests/137.

Athena can benefit from partitioning scheme which provides partition elimination based on one or more query predicate.

https://docs.aws.amazon.com/athena/latest/ug/partitions.html

Perhaps as a "v2" feature, would be good to have some support for this capability.

Athena also supports JSON/JSONL as a file format. This option may address issue #6 (CSV column shift issue).

See Athena documentation: https://docs.aws.amazon.com/athena/latest/ug/parsing-JSON.html

Adding support for JSON format would involve the following:

• adding support for specifying different formats
• adding JSON writer logic
• adding JSON format specific Athena table definition

Example of JSON table definition:

CREATE EXTERNAL TABLE json_table (
  column_a string,
  column_b int
 )
 ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
 WITH SERDEPROPERTIES ('ignore.malformed.json' = 'true')
 LOCATION 's3://bucket/path/';