Loosing table attribute when creating partition using it on partitionKeys.

Question

Im loosing my table attributes when using them as table partitions. below the code used to write dynamic frame with partitions.

```
## write dynamic frame to glue catalog
glueContext.write_dynamic_frame.from_options(
    frame=dyf, connection_type="s3",
    connection_options={"path": "s3://project_data/onesearch-logs-{}/".format(environment), "partitionKeys": ["endpoint","region","date"]},
    format_options={"compression": "snappy", "useGlueParquetWriter": "true"},
    format="parquet", transformation_ctx="writer")
```

I can see the s3 folder hierarchy correctly created as per below

`s3://project_data/onesearch-logs-dev/endpoint=autocomplete/region=ap_southeast_1/date=2023-04-10/part-00000-17b869f5-0400-469d-9866-e8e630f7b378.c000.snappy.parquet`

After crawling and cataloging my folder ` s3://project_data/onesearch-logs-dev/` i loose the attribute `region` in my table** 'dev_endpoint_autocomplete'**

Also when I try to read from_catalog using the `region` as `push_down_predicate` as per code below I get the error below

**CODE :**
```
dyf_ac = glueContext.create_dynamic_frame.from_catalog(database="searchdb"
                                                         , table_name=**'dev_endpoint_autocomplete'**
                                                         , transformation_ctx="reader"
                                                         , push_down_predicate="region==ap_southeast_1 and date=='2023-03-14'"
                                                         , additional_options={"catalogPartitionPredicate":"endpoint='autocomplete'"}
                                                         )
```

**ERROR: **
 `com.amazonaws.services.glue.model.InvalidInputException: Unknown column 'endpoint' (Service: AWSGlue; Status Code: 400; Error Code: InvalidInputException; Request ID:`

I also tried the below variant for push_down_predicate but failed.

```
dyf_ac = glueContext.create_dynamic_frame.from_catalog(database="searchdb"
                                                         , table_name=**'dev_endpoint_autocomplete'**
                                                         , transformation_ctx="reader"
                                                         , push_down_predicate="endpoint='autocomplete' AND region='ap_southeast_1' AND date='2023-03-14'"
                                                         , additional_options={"catalogPartitionPredicate":"endpoint='autocomplete'"}
                                                         )
```

also

```
, push_down_predicate="endpoint='autocomplete' AND region='ap_southeast_1' AND date='2023-03-14'"
, push_down_predicate="endpoint=autocomplete AND region=ap_southeast_1 AND date=2023-03-14"
```

**ERROR: **
`com.amazonaws.services.glue.util.NonFatalException: User's pushdown predicate: endpoint='autocomplete AND region=ap_southeast_1 AND date='2023-03-14' can not be resolved against partition columns: [date,region]
`

Not sure if im doing something wrong or if my assumptions on the usage of push_down_predicates are somewhat incorrect.

Thanks

shubham · Answer

Hello,

I created the below sample data:

|id|first_name|last_name|email|endpoint|region|date|
| --- | --- | --- | --- | --- | --- | --- |
|1|Reta|Pitrasso|rpitrasso0@ox.ac.uk|Alphazap|Portugal|5/13/2022|
|2|Consolata|Pressey|cpressey1@ebay.co.uk|Regrant|Vietnam|3/13/2023|
|3|Fabe|Porte|fporte2@free.fr|Cardify|Netherlands|7/1/2022|
|4|Alvy|Tabord|atabord7@microsoft.com|Ronstring|China|1/7/2023|

I read the data using DynamicFrame and written the data back to S3 location using the same code as yours in parquet file format:

```
## write dynamic frame to glue catalog
S3bucket_node3 = glueContext.write_dynamic_frame.from_options(
    frame=ApplyMapping_node2, connection_type="s3",
    connection_options={"path": "s3:///project_data/onesearch-logs/", "partitionKeys": ["endpoint","region","date"]},
    format_options={"compression": "snappy", "useGlueParquetWriter": "true"},
    format="parquet", transformation_ctx="S3bucket_node3")
```
Got the expected output at the target S3 location(added one sample output):

```
s3:///project_data/onesearch-logs/endpoint=Alphazap/region=Portugal/date=5%2F13%2F2022/

```

After that I created a Crawler to Crawl 's3:///project_data/onesearch-logs/'  data source and I can see the correct number of columns along with partition columns

```
[
  {
    "Name": "id",
    "Type": "string"
  },
  {
    "Name": "first_name",
    "Type": "string"
  },
  {
    "Name": "last_name",
    "Type": "string"
  },
  {
    "Name": "email",
    "Type": "string"
  },
  {
    "Name": "endpoint",
    "Type": "string",
    "PartitionKey": "Partition (0)"
  },
  {
    "Name": "region",
    "Type": "string",
    "PartitionKey": "Partition (1)"
  },
  {
    "Name": "date",
    "Type": "string",
    "PartitionKey": "Partition (2)"
  }
]
```

Also, I used the push_down_predicite to read DynamicFrame and I was able to see the correct results:

```
dyf_ac = glueContext.create_dynamic_frame.from_catalog(database="default"
                                                         , table_name='onesearch_logs'
                                                         , transformation_ctx="reader"
                                                         , push_down_predicate="region=='China'"
                                                         , additional_options={"catalogPartitionPredicate":"endpoint='Ronstring'"}
                                                         )
dyf_ac.toDF().show()
```
Output:

| id|first_name|last_name|               email| endpoint|region|        date|
| --- | --- | --- | --- | --- | --- | --- |
|  8|      Alvy|   Tabord|atabord7@microsof...|Ronstring| China|1%2F7%2F2023|

Therefore, I would like to inform you that your assumption and usage of the push down predicate is correct. However, I suspect the issue is at the Crawler stage. Please check your Crawler logs/S3 parquet data to understand the reason why the column 'region' is not getting added to the Data Catalog table.

That being said, if you would like resource based troubleshooting, please raise a support case with AWS for further information and the support team will get back to you with the root cause on the support ticket.

Thank you!

Loosing table attribute when creating partition using it on partitionKeys.

Add your answer

Relevant content

id	first_name	last_name	email	endpoint	region	date
1	Reta	Pitrasso	rpitrasso0@ox.ac.uk	Alphazap	Portugal	5/13/2022
2	Consolata	Pressey	cpressey1@ebay.co.uk	Regrant	Vietnam	3/13/2023
3	Fabe	Porte	fporte2@free.fr	Cardify	Netherlands	7/1/2022
4	Alvy	Tabord	atabord7@microsoft.com	Ronstring	China	1/7/2023