Questions tagged with AWS Glue

I am reading a parquet file from S3 in AWS Glue job and creating a Dynamic Frame. I have a requirement of enforcing schema on it before writing the data to a Redshift table. I have the required schema in a Glue Data Catalog table. But, for enforcing the schema the below piece of code did not work using resolvechoice transform. Am I doing something wrong or does this option not work? S3bucket_node1_resolveChoice = S3bucket_node1.resolveChoice(choice='MATCH_CATALOG',database='redshift',table_name='target_table')

Using AWS Glue jobs, I want to insert, update and delete the rows in a Table in RDS Database based on the primary key. How do I achieve it.

Hi, I'm trying to iterate through the results returned from SelectFields action using the following command ``` SelectFields_node1656679636429.toDF().index ``` but i get the following error > AttributeError: 'DataFrame' object has no attribute 'index' it appears that even I convert the DynamicFrame to DataFrame, many attributes are missing. I found a workaround using RDD, and re-inialising a new DataFrame, like below ``` rdd = SelectFields_node1656679636429.toDF().rdd df = pd.DataFrame(rdd.collect()) df.index ``` but this approach seems an overhead. Are there any better, native ways of getting the properly initialized DataFrame from DynamicFrame?

Are there any ways to read delta lake files from s3 and create Data catalog on top of this to run Glue ETL job? When I crawl in delta folders it creates separate schema for log, manifest & parquets rather then each tables with all the delta log, manifest files and parquet files , https://docs.aws.amazon.com/glue/latest/dg/crawler-data-stores.html says Crawler now have native client then why it is not recognizing the path , Am I missing something.

Hi, I'm using AWS Glue Studio and once I click "data preview" it fails with the following error. The flow consists of 2 actions - PosgtreSQL JDBC Data source and Select Field action. The error is thrown instantly once the "Data Preview" button is clicked. The overall flow run successfully if i click "RUN" button - there are no errors and I get the outcome if I add the Target Source to dump the results back to PosgtreSQL table. It's just a Data Preview functionality that fails. Any idea what could be wrong and how to troubleshoot it? > Py4JJavaError: An error occurred while calling o538.getSampleDynamicFrame. : java.lang.UnsupportedOperationException: empty.reduceLeft at scala.collection.TraversableOnce$class.reduceLeft(TraversableOnce.scala:180) at scala.collection.AbstractTraversable.reduceLeft(Traversable.scala:104) at scala.collection.TraversableOnce$class.reduce(TraversableOnce.scala:208) at scala.collection.AbstractTraversable.reduce(Traversable.scala:104) at com.amazonaws.services.glue.SparkSQLDataSource.com$amazonaws$services$glue$SparkSQLDataSource$$getPaths(DataSource.scala:724) at com.amazonaws.services.glue.SparkSQLDataSource$$anonfun$getSampleDynamicFrame$7.apply(DataSource.scala:799) at com.amazonaws.services.glue.SparkSQLDataSource$$anonfun$getSampleDynamicFrame$7.apply(DataSource.scala:793) at com.amazonaws.services.glue.util.FileSchemeWrapper$$anonfun$executeWithQualifiedScheme$1.apply(FileSchemeWrapper.scala:89) at com.amazonaws.services.glue.util.FileSchemeWrapper$$anonfun$executeWithQualifiedScheme$1.apply(FileSchemeWrapper.scala:89) at com.amazonaws.services.glue.util.FileSchemeWrapper.executeWith(FileSchemeWrapper.scala:82) at com.amazonaws.services.glue.util.FileSchemeWrapper.executeWithQualifiedScheme(FileSchemeWrapper.scala:89) at com.amazonaws.services.glue.SparkSQLDataSource.getSampleDynamicFrame(DataSource.scala:792) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244) at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357) at py4j.Gateway.invoke(Gateway.java:282) at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132) at py4j.commands.CallCommand.execute(CallCommand.java:79) at py4j.GatewayConnection.run(GatewayConnection.java:238) at java.lang.Thread.run(Thread.java:748)

I'm trying to use newest package for Amazon integration in MWAA. In this particular case I want to use `GlueJobOperator` which is a part of the latest `apache-airflow-providers-amazon` package. ([Link to the documentation](https://airflow.apache.org/docs/apache-airflow-providers-amazon/4.0.0/operators/glue.html)) MWAA Airflow version: ` 2.2.2 ` I added this to the *requirements.txt*: ``` apache-airflow-providers-amazon==4.0.0 ``` and tried to import it and use it like in the examples: ``` from airflow.providers.amazon.aws.operators.glue import GlueJobOperator glue_job = GlueJobOperator( task_id='airflow_id', job_name='job_name' wait_for_completion=True, script_location='bucket_script_prefix', s3_bucket='bucket_name', iam_role_name='iam_role' create_job_kwargs=job_arguments, script_args=script_arguments ) ``` Unfortunately, whenever DAG is parsed I get this error: ``` ... from airflow.providers.amazon.aws.operators.glue import GlueJobOperator ImportError: cannot import name 'GlueJobOperator' from 'airflow.providers.amazon.aws.operators.glue' (/usr/local/lib/python3.7/site-packages/airflow/providers/amazon/aws/operators/glue.py) ``` It's not my first rodeo with MWAA and some extra packages, plugins etc. but I am lost. In this case, I tried many things, went through docs from cover to cover and I still couldn't find the reason. I verified in MWAA that packages were successfully installed both in the logs and in the UI with the version prompted in *requirements.txt*. | Package Name | Version | Description | | --- | --- | --- | | apache-airflow-providers-amazon |4.0.0 |Amazon integration (including Amazon Web Services (AWS)). | Fun fact: I'm using `S3Hook` in some other DAGs and it parses just fine. ``` from airflow.providers.amazon.aws.hooks.s3 import S3Hook s3_hook = S3Hook() ... ```

We are doing ETL operation from Redshift to Redshift using AWS Glue. We need to do incremental operation. We need to identify latest modified records from source and process only new and latest modified records(recent updates) from source to avoid reprocessing of millions of records. We have following key columns in source: 1. ID(primary key column), 2. CreatedDate(timestamp in which record has created), 3. UpdatedDate(timestamp in which record has updated. Initially, it will be null) Is there any possibility to process only latest records, instead on re-processing all the records. Much appreciated for possible solutions.

While running AWS glue Job facing this error

Hello there: We have ingest jobs A, B, C, and D and Transformation jobs X and Y in PySpark For Workflow 1: the steps are: 1. Once Job A, B, and C successfully done 2. Start transformation job X For Workflow 2: the steps are: 1. Once job A, C, and D are successfully done 2. Start transformation job Y Jobs A, B, C, and D need to run only once (if it runs in Workflow 1, it should nor run in Workflow 2) Does Glue job workflow support this? Please advise Thanks a lot in advance Li

I have subscribed to AWS Marketplace Glue Hudi Connector and added a Hudi Connector in Glue. I created a visual based Glue ETL job which reads data from S3 and merge the data to another S3 using Hudi connector. in the Hudi connector component, I added 'path' and 'hoodie.table.name' as connection options. But my job is failing with the error "hoodie only support org.apache.spark.serializer.KryoSerializer as spark.serializer". Can anyone help me on this?

I need to pre-process some data on S3 before the Glue Crawler crawls the data. For this I created an S3 Object Lambda to do the pre-processing. If I test the Object Lambda using the CLI, it provides the correct (processed) file. I then tried to use the ARN of the Object Lambda as the data store. However the Include Path prompt will only accept a value starting with 's3://'. I also tried to use an Access Point alias, but I do not see the alias for a Object Lambda Access Point. (If I use the alias for the supporting access point, I get the un-processed data from the S3 bucket.) As a last resort, I also tried to use terraform to set the data store. This did work, but when I run the crawler I get an error. I assume this is also because the crawler is expecting a path starting with 's3://' and not an ARN. Is there a different way to use an Object Lambda as the Data Store for a Glue Crawler?

Hello, I'm using Hudi connector in Glue, first, I bulk inserted the initial dataset to Hudi table, I'm adding a daily incremental records and I can query them using Athena, what I'm trying to do is to get the newly added, updated or deleted records in a separate parquet file. I've tried different approaches and configurations using both copy on write and merge on read tables but still can get the updates in a separate file. I used these configurations in different combinations: 'className' : 'org.apache.hudi', 'hoodie.datasource.hive_sync.use_jdbc': 'false', 'hoodie.datasource.write.precombine.field': 'ts', 'hoodie.datasource.write.recordkey.field': 'uuid', 'hoodie.payload.event.time.field': 'ts', 'hoodie.table.name': 'table_name', 'hoodie.datasource.hive_sync.database': 'hudi_db', 'hoodie.datasource.hive_sync.table': 'table_name', 'hoodie.datasource.hive_sync.enable': 'false', # 'hoodie.datasource.write.partitionpath.field': 'date:SIMPLE', 'hoodie.datasource.write.hive_style_partitioning': 'true', 'hoodie.meta.sync.client.tool.class': 'org.apache.hudi.aws.sync.AwsGlueCatalogSyncTool', 'hoodie.datasource.write.table.type': 'COPY_ON_WRITE', 'path': 's3://path/to/output/', # 'hoodie.datasource.write.operation': 'bulk_insert', 'hoodie.datasource.write.operation': 'upsert', # 'hoodie.datasource.hive_sync.partition_extractor_class': 'org.apache.hudi.hive.NonPartitionedExtractor', # 'hoodie.datasource.hive_sync.partition_extractor_class': 'org.apache.hudi.hive.MultiPartKeysValueExtractor', 'hoodie.datasource.write.keygenerator.class': 'org.apache.hudi.keygen.NonpartitionedKeyGenerator', # 'hoodie.compaction.payload.class': 'org.apache.hudi.common.model.OverwriteWithLatestAvroPayload', # 'hoodie.cleaner.policy': 'KEEP_LATEST_COMMITS', 'hoodie.cleaner.delete.bootstrap.base.file': 'true', "hoodie.index.type": "GLOBAL_BLOOM", 'hoodie.file.index.enable': 'true', 'hoodie.bloom.index.update.partition.path': 'true', 'hoodie.bulkinsert.shuffle.parallelism': 1, # 'hoodie.datasource.write.keygenerator.class': 'org.apache.hudi.keygen.CustomKeyGenerator' Thank you.

In the [documentation](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-python-libraries.html) I can see that we can add additional modules from pip using the following command %additional_python_modules nameOfModule However, I would like to be using a later version of pandas and pyarrow than the ones currently installed, 1.0.4 and 0.16.0 respectively. I have tried adding this at the top of the script in a notebook: ``` %additional_python_modules pandas==1.4.2, pyarrow==8.0.0 ``` As well as passing these as parameters when doing a manual run, however, I still see the version numbers remaining as the standard out of the box versions, is this possible to do?

Hello, Currently I am trying to read csv files in my s3 bucket with the following format: ``` header 1, header 2, header 3 value 1, value 2, value 3 header 1, header 2, header 3, header 4 value 1, value 2, value 3, value 4 ``` as you can see I have two header rows. But I only care about the second header row and everything below. Is there a way to ignore the first two lines with glue and use the second header row as a starting point to create a table?

I have created a templated job (with parameters) to ingest data from different tables (passing the database and table as parameter) and write the data to S3 (passing the destination bucket as parameter as well). Now I would like to create a workflow where I can add the job multiple times, once for each table I need to ingest from. But in the Workflow UI, when I try to add the same job again, it doesn't add a new block for the job. How should I approach to solve this?

I am using EMR 6.6.0, which has hudi 10.1. I am trying to bulkinsert and do inline clustering using Hudi. But seems its not clustering the file as per file size being mentioned. But it is still producing the files in KB only. I tried below configuration: > hudi_clusteringopt = { 'hoodie.table.name': 'myhudidataset_upsert_legacy_new7', 'hoodie.datasource.write.recordkey.field': 'id', 'hoodie.datasource.write.partitionpath.field': 'creation_date', 'hoodie.datasource.write.precombine.field': 'last_update_time', 'hoodie.datasource.hive_sync.enable': 'true', 'hoodie.datasource.hive_sync.database': 'my_hudi_db', 'hoodie.datasource.hive_sync.table': 'myhudidataset_upsert_legacy_new7', 'hoodie.datasource.hive_sync.partition_fields': 'creation_date', 'hoodie.datasource.hive_sync.partition_extractor_class': 'org.apache.hudi.hive.MultiPartKeysValueExtractor', "hoodie.datasource.write.hive_style_partitioning": "true", "hoodie.datasource.write.operation": "bulk_insert", } # "hoodie.datasource.write.operation": "bulk_insert", try: inputDF.write.format("org.apache.hudi"). \ options(**hudi_clusteringopt). \ option("hoodie.parquet.small.file.limit", "0"). \ option("hoodie.clustering.inline", "true"). \ option("hoodie.clustering.inline.max.commits", "0"). \ option("hoodie.clustering.plan.strategy.target.file.max.bytes", "1073741824"). \ option("hoodie.clustering.plan.strategy.small.file.limit", "629145600"). \ option("hoodie.clustering.plan.strategy.sort.columns", "pk_col"). \ mode('append'). \ save("s3://xxxxxxxxxxxxxx"); except Exception as e: print(e) Here is the data set if someone wants to regenerate: inputDF = spark.createDataFrame( [ ("1001",1001, "2015-01-01", "2015-01-01T13:51:39.340396Z"), ("1011",1011, "2015-01-01", "2015-01-01T12:14:58.597216Z"), ("1021",1021, "2015-01-01", "2015-01-01T13:51:40.417052Z"), ("1031",1031, "2015-01-01", "2015-01-01T13:51:40.519832Z"), ("1041",1041, "2015-01-02", "2015-01-01T12:15:00.512679Z"), ("1051",1051, "2015-01-02", "2015-01-01T13:51:42.248818Z"), ], ["id","id_val", "creation_date", "last_update_time"] )

In AWS Glue I use a legacy Python package that reads a constant json file from the same package. To simplify things, let's say `testLib` package has a `test-lib.py` and `data.json` files. `test-lib.py` has a function: ``` def test(): f = open('data.json') ``` I uploaded testLib.zip to S3 and use it in the AWS Glue job: ``` from test_lib import test test() ``` The AWS Glue job fails with the error: `FileNotFoundError: [Errno 2] No such file or directory: 'data.json'` There are many questions about how to manage files on S3 in AWS Glue. But it requires changing the legacy package that I don't want to do. Is there any way to configure AWS Glue job to allow open 'local' files?

Hello. I'm working with glue studio. I'm having a hard time to get output schema from the steps. I'm trying to use the Data Preview to be able to see the schema but since the data is too big, I got a timeout error. How can I get the output schema at a faster way?

I have a AWS Glue pyspark ETL job and I need to load a redshift table from the dataframe. Several date and string columns have NULL values in the DataFrame in a subset of rows. I am getting this error when trying to run the `glueContext.write_dynamic_frame.from_catalog` command in the glue pyspark script... `IllegalArgumentException: Don't know how to save NullType to REDSHIFT` I am not sure how to resolve as I cannot "fill" these missing values. They are simply optional dates and values that do not exist. I can find no documentation or information in AWS about how to resolve this issue.

Running a job to fetch data from S3 and write to GCP BQ using Glue BQ connector by AWS. Everything else is fine, but for one table the second runs seems to fail always with below error. First time it runs fine, I have bookmarks enabled to fetch new data added in S3 and write to BQ. **It fails with below error on write function.** Unable to understand the null pointer exception thrown. ``` Caused by: java.lang.NullPointerException at com.google.cloud.bigquery.connector.common.BigQueryClient.loadDataIntoTable(BigQueryClient.java:532) at com.google.cloud.spark.bigquery.BigQueryWriteHelper.loadDataToBigQuery(BigQueryWriteHelper.scala:87) at com.google.cloud.spark.bigquery.BigQueryWriteHelper.writeDataFrameToBigQuery(BigQueryWriteHelper.scala:66) ... 42 more ```

Hello, I am having a `pyspark` application adding partitions (dynamic overwriting) to a AWS Glue table using `insertInto` method. Upon completion of the task, a global `.SUCCESS` file in the top level directory in `S3` is being updated with the timestamp. My desired behaviour would be to have `.SUCCESS` files with timestamp inside the updated partition instead of the top level directory. Is this possible? Best, N

Hello, I am trying to query some files stored in s3. The files are stored in binary Amazon Ion format and have gzip compression. When attempting to preview my table: "SELECT * FROM "*hidden*"."test_ion_data" limit 10;" I get this error: "HIVE_CURSOR_ERROR: Unknown symbol text for $10" I have populated many of the table properties manually as glue was not updating much. I am thinking that either the data itself is malformed (glue not populating table info would reaffirm this).

Hello, I am trying to use Glue to take an input file, do my required transformations, then output the columns in a specific order. I also want to output columns that may not be present in the input and have those columns just be null as placeholders. For example, I ingest columns CustomerID, Customer Name, DoB in this order. I want to output a file that goes: DoB, Customer ID, Customer Phone Number, Customer Address, Customer Name. Essentially I have a template that I have to match my output's format to but my input may not have every column, which is fine, but I still need at least placeholder columns there in order to match my template and for all my columns to be in a specific order. Is there a better way to do this then a custom SQL statement node? I would ideally like to be able to view the input schema against the template output schema and just point the relevant inputs to map

I created glue catalog external schema on Redshift serverless .Both Glue and redshift are in same location . ---------------------- create external schema dojo from data catalog database 'stream' iam_role 'arn:aws:iam::XXX' ----------------------- I successfully created schema but tables under 'stream' catalog database aren't getting listed . -Above Role which i associated to serverless redshift trusted relationship: { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": [ "redshift-serverless.amazonaws.com", "redshift.amazonaws.com" ] }, "Action": "sts:AssumeRole" } ] } policies: -AmazonS3FullAccess -AWSGlueConsoleFullAccess -AmazonRedshiftAllCommandsFullAccess I have gone through below question but i couldn't find the solution. [https://repost.aws/questions/QU6YMBY70wT0mLSVviOkkQLw/redshift-external-schema]() Then i created same ext schema on redshift provisioned with respective role , then im able too see tables in stream catalog database.

I'm trying to run a Glue Workflow from the lambda. According to [this documentation](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-workflow.html#aws-glue-api-workflow-StartWorkflowRun) there is a parameter `RunProperties`. So, I'm trying to run the workflow like this: ``` newRun = glue.start_workflow_run(Name = 'MyWorkflow', RunProperties = { 'key1': 'value1' }) ``` But getting the error: ``` [ERROR] ParamValidationError: Parameter validation failed: Unknown parameter in input: "RunProperties", must be one of: Name ``` It looks like it waits only one parameter `Name` but not `RunProperties`. The question is: How can I run my workflow with parameters from a lambda function? Some additional information: What I'm trying to achieve here is to run a Glue Workflow when a zip file uploaded to S3. I don't want to run a crawler because it's redundant here. When a zip file is uploaded to S3, a lambda is started and from it I want to start the Glue Workflow.

I was doing a POC using Glue to Migrate data from RDS MySql to RDS Postgres. I have created Connectors to both source and target, and a crawler which connected to source. Then created a job and tried to migrate data with out any transformation and started getting java.sql.SQLIntegrityConstraintViolationException: Duplicate entry error java.lang.reflect.Constructor.newInstance(Constructor.java:423)\n\tat com.mysql.cj.util.Util.handleNewInstance(Util.java:192)\n\tat com.mysql.cj.util.Util.getInstance(Util.java:167)\n\tat com.mysql.cj.util.Util.getInstance(Util.java:174)\n\tat com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)\n\tat com.mysql.cj.jdbc.ServerPreparedStatement.executeBatchSerially(ServerPreparedStatement.java:385)\n\tat com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:435)\n\tat com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:794)\n\tat org.apache.spark.sql.jdbc.glue.GlueJDBCSink$.saveBatch(GlueJDBCSink.scala:400)\n\tat org.apache.spark.sql.jdbc.glue.GlueJDBCSink$.$anonfun$saveTable$4(GlueJDBCSink.scala:77)\n\tat org.apache.spark.sql.jdbc.glue.GlueJDBCSink$.$anonfun$saveTable$4$adapted(GlueJDBCSink.scala:77)\n\tat org.apache.spark.sql.jdbc.glue.GlueJDBCSink$.savePartition(GlueJDBCSink.scala:261)\n\tat org.apache.spark.sql.jdbc.glue.GlueJDBCSink$.$anonfun$saveTable$3(GlueJDBCSink.scala:77)\n\tat org.apache.spark.sql.jdbc.glue.GlueJDBCSink$.$anonfun$saveTable$3$adapted(GlueJDBCSink.scala:76)\n\tat org.apache.spark.rdd.RDD.$anonfun$foreachPartition$2(RDD.scala:1020)\n\tat org.apache.spark.rdd.RDD.$anonfun$foreachPartition$2$adapted(RDD.scala:1020)\n\tat org.apache.spark.SparkContext.$anonfun$runJob$5(SparkContext.scala:2278)\n\tat org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:90)\n\tat org.apache.spark.scheduler.Task.run(Task.scala:131)\n\tat org.apache.spark.executor.Executor$TaskRunner.$anonfun$run$3(Executor.scala:497)\n\tat org.apache.spark.util.Utils$.tryWithSafeFinally(Utils.scala:1439)\n\tat org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:500)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat ****java.lang.Thread.run(Thread.java:750)\nCaused by: java.**sql.SQLIntegrityConstraintViolationException: Duplicate entry '00002b0b-1a34-3319-b003-fb073fb8248d' for key 'transformer_fragment.PRIMARY'\n\tat ******com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)\n\tat com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)\n\tat com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:637)\n\tat com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:418)\n\tat com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)\n\tat com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)\n\tat com.mysql.cj.jdbc.ServerPreparedStatement.executeBatchSerially(ServerPreparedStatement.java:357)\n\t... 19 more\n","Accumulator Updates":[{"ID":130,"Update":"105","Internal":false,"Count Failed Values":true},{"ID":132,"Update":"0","Internal":false,"Count Failed Values":true},{"ID":139,"Update":"0","Internal":false,"Count Failed Values":true},{"ID":140,"Update":"1","Internal":false,"Count Failed Values":true},{"ID":141,"Update":"0","Internal":false,"Count Failed Values":true},{"ID":142,"Update":"0","Internal":false,"Count Failed Values":true},{"ID":143,"Update":"1135","Internal":false,"Count Failed Values":true},{"ID":144,"Update":"0","Internal":false,"Count Failed Values":true},{"ID":145,"Update":"1","Internal":false,"Count Failed Values":true}]},"Task Info":{"Task ID":13,"Index":5,"Attempt":1,"Launch Time":1655403050036,"Executor ID":"1","Host":"172.31.22.88","Locality":"NODE_LOCAL","Speculative":false,"Getting Result Time":0,"Finish Time":1655403050162,"Failed":true,"Killed":false,"Accumulables": The following are the values I have used while creating the job, Type : Spark Glue Version - Glue 3.0 Supports spark3.1, Scala2, Python3 Language - Python3 Worker Type - G.1X Automatically Scale the number of workers - False Requested number of workers - 2 Generate Job Insights - True Job Bookmark - Enable Number of retries - 1 job timeout - 2880 Really appreciate any help !

I have a parameterized glue job , that will be called in parallel (25 glue job) through step functions, when bookmark enabled , version mismatch exception is thrown, when disabled, it runs fine. . Below are the inputs to the step function { "JobName": "gluejobname3", "Arguments": { "--cdb": "catalogdbname", "--ctbl": "tablename", "--dest": "s3://thepath/raw/", "--extra-py-files": "s3://thepath/mytransformation.py.zip" } } When bookmarks are disabled, the step functions calls the parameterized glue job, and loads the data into the different s3 location. below is the glue job script --------------- import sys from awsglue.transforms import * from awsglue.utils import getResolvedOptions from pyspark.context import SparkContext from awsglue.context import GlueContext from awsglue.job import Job import mytransformation args = getResolvedOptions(sys.argv, ["JOB_NAME","cdb","ctbl","dest"]) sc = SparkContext() glueContext = GlueContext(sc) spark = glueContext.spark_session job = Job(glueContext) # this was given so that while it was initialized each job would have their unique id job.init(args["JOB_NAME"]+args["ctbl"], args) #Custom parameters that will be dynamically passed through with the call to the parameterized glue job db = args["cdb"] tbl = args["ctbl"] dest = args["dest"] # Script generated for node MySQL table #dynamically creating a variable name. so that transformation_ctx would be unique for each glue job globals()[f'MySQLtable_sourceDf{tbl}'] = glueContext.create_dynamic_frame.from_catalog( database=db, table_name=tbl, transformation_ctx = '"'+f'MySQLtable_sourceDf{tbl}'+'"' ) #passing the same transformation_ctx into the destination frame destination_Df = mytransformation.tansform(globals()[f'MySQLtable_sourceDf{tbl}']) # Script generated for node Amazon S3 # : "s3://anvil-houston-preprod" #creating the dynamic unique transformation ctx name since the jobs will be run concurrently globals()[f'S3bucket_node{tbl}'] = glueContext.write_dynamic_frame.from_options( frame=destination_Df, connection_type="s3", format="glueparquet", connection_options={"path": dest, "partitionKeys": []}, format_options={"compression": "snappy"}, transformation_ctx='"'+f'S3bucket_node{tbl}'+'"' ) job.commit() ------ Above runs fine , while the execution is started through step functions ( 25 parallel parameterized glue job), the job runs fine, and loads to 25 diffferent locations. When bookmark is now enabled, the job fails with version mismatch . An error occurred while calling z:com.amazonaws.services.glue.util.Job.commit. Continuation update failed due to version mismatch. Expected version 1 but found version 2 (Service: AWSGlueJobExecutor; Status Code: 400; Error Code: VersionMismatchException; Please help

how do we version control an aws glue workflow ?

is glue defacto in running ETL jobs in aws cloud. spinning an ec2 instance sounds simple and do whatever we want to do with the data. are there any special aws glue is used for?

Hi, I have multiple batching data sources (in JSON format) in S3 and I want to load it into Redshift via AWS Glue. My use case is to parse and join those multiple data sources into a final table, then insert it into a table in Redshift. My question is: 1. If I create a workflow in AWS Glue and make it runs once a day, can it continuously update(like insert new rows) to the table in Redshift? 2. I knew that AWS Glue has a feature that could automatically detect data source schema changes, but what happened when it detects the changes? will it create a new table in Redshift? will I be able to get notification when schema changed?

Hi, We are trying to use Athena as our consumption service. We have migrated most of the hive databases/tables from external Hive meta store to AWS Glue except those database that has Hive ACID tables because Glue don't support Hive ACID tables. To read Hive ACID tables from Athena, we have configured Athena connector for Hive based this article https://docs.aws.amazon.com/athena/latest/ug/connect-to-data-source-hive.html and used AthenaHiveMetastoreFunctionWithLayer jar. When try to query Hive ACID table (based on ORC file format ) from Athena using newly created custom catalog for Hive, I get below error. "HIVE_CURSOR_ERROR: Failed to read ORC file: s3://my-datalake-bkt-dev/test/acid/ug/base_0000002/bucket_00000" It looks like Athena not able to read the hive ACID file format. Can some one please help me?

I'm using a Notebook together with a Glue Dev Endpoint to load data from S3 into a Glue DynamicFrame. The `printSchema` method works fine but the `show` method yields nothing although the dataframe is not empty. Converting the DynamicFrame into a Spark DataFrame actually yields a result (`df.toDF().show()`). Here the dummy code that I'm using ```python glueContext = GlueContext(spark.sparkContext) df = glueContext.create_dynamic_frame_from_options( connection_type="s3", connection_options = { "paths": [f"s3://bucketname/filename"]}, format="json", format_options={"multiline": True} ) df.printSchema() df.show() ``` This problem was posted on Stackoverflow before: https://stackoverflow.com/questions/56013334/spark-dynamic-frame-show-method-yields-nothing Why does the `show` method yield nothing? Is this a bug or is there something that I'm missing to make this work?

We are planning for a POC with AWS Glue between two different environments. In our case, Aurora MySQL (Read Instance) is Source and Redshift is Target. Note: This involves only extract and load operation, it doesn't involve any transformation. We have following clarification/details needed: 1. How to create source connection for MySQL read replica? 2. What are the configuration/services(other than AWS Glue) required in AWS, for reading from MySQL read replica? 3. Our read replica instance is already used by one of the microservice. Will it have any impact if same instance is used by AWS Glue? 4. Will AWS Glue expects to create any tables or log files in Source ? 5. Any configuration changes needed on Source for Glue to Read MySQL and Load to Redshift? 6. Is it possible to connect source read replica using JDBC URL? 7. Does AWS Glue detect incremental changes from Source (MySQL)? 8. Does AWS Glue lock the table in Source MySQL Aurora while reading incremental changes? Thanks in advance

I have a glue job that write to a Data Catalog. In the Data Catalog I originally set it up as CSV, and all works fine. Now I would like to try to use Parquet for the Data Catalog. I thought I would just have to re-create the table and select Parquet instead of CSV, so I did so like so: ``` CREATE EXTERNAL TABLE `gp550_load_database_beta.gp550_load_table_beta`( `vid` string, `altid` string, `vtype` string, `time` timestamp, `timegmt` timestamp, `value` float, `filename` string) PARTITIONED BY ( `year` int, `month` int, `day` int) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' LOCATION 's3://ds905-load-forecast/data_store_beta/' TBLPROPERTIES ( 'classification'='parquet') ``` I left my glue job unchanged. I have it sending its output to the Data Catalog Table like so: ``` additionalOptions = {"enableUpdateCatalog": True, "updateBehavior": "LOG"} additionalOptions["partitionKeys"] = ["year", "month", "day"] # Data Catalog WRITE DataCatalogtable_node2 = glueContext.write_dynamic_frame.from_catalog( frame = dynamicDF, database = db_name, table_name = tbl_name, additional_options=additionalOptions, transformation_ctx = "DataCatalogtable_node2", ) ``` When I checked the files being created by the Data Catalog in s3://ds905-load-forecast/data_store_beta/, they look to just be CSV. What do I need to do to use Parquet? Can I just change the sink routine to use glueContext_write_dynamic_frame.from_options()?

I have a csv file with 5 million rows in my s3. I used crawler on the file. I have a custom transformations in my glue job. My issue is, if I use create_dynamic_frame_from_catalog(), it is running very slow, where as if I use create_sample_dynamic_frame_from_catalog() with max sample limit as 5 million, it is working much faster. Why it is happening. i want to speed up the job without using the sample method.

I am setting up a new data lake and have been tasked with creating the master data tables in the data bricks delta lake component. I'm trying to do this in a use-case agnostic way (or as agnostic as possible), and need to automate the process where possible. I have researched aws glue crawlers, and it seems it is a good way to automatically create a schema and catalog for the data. However, I'm not sure how to proceed. I'm assuming that creating the master data means identifying common fields in all the data sources and creating a schema for all the data using a single crawler, and then dividing this schema into facts and dimensions. After that I could use spark jobs on data bricks to extract what I need from the raw data and to populate the master data, while checking for duplicates and doing whatever other transformations that need to be done. This plan seems like it requires a lot of manual labor though, and it's not use case agnostic in any way. Does anyone know how it could be automated further? Any help would be much appreciated.

``` AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: org.apache.hadoop.hive.ql.metadata.HiveException: MetaException(message:Unable to build AWSGlueClient: com.amazonaws.SdkClientException: Unable to find a region via the region provider chain. Must provide an explicit region in the builder or setup environment to supply a region.) ``` Getting this error when defining spark views in AWS Glue local development using Docker image. Below is a function that I have defined and trying to call in the PySpark code ,which results in this error. ``` def incrementalTransform(glueContext, dfc) -> DynamicFrameCollection: input = dfc.select(list(dfc.keys())[0]).toDF() target = dfc.select(list(dfc.keys())[1]).toDF() targetWithColumns = ( target.withColumn("rownum", lit(0)) .withColumn("partition_year", year("create_time")) .withColumn("partition_month", month("create_time")) .withColumn("partition_day", dayofmonth("create_time")) ) inputView = input.createOrReplaceTempView("inputView") targetView = targetWithColumns.createOrReplaceTempView("outputView") r1 = spark.sql("(select * from inputView)") r2 = spark.sql("(select * from targetView)") result = spark.sql("(select * from inputView) UNION All (select * from targetView)") r1.show() r2.show() result.show() ```

I have a Glue ETL job which creates partitions during the job ``` additionalOptions = {"enableUpdateCatalog": True, "updateBehavior": "LOG"} additionalOptions["partitionKeys"] = ["year", "month", "day"] ``` I don’t have it Update the Data Catalog because doing so changes all my Table Data Types. So after I am done, the way I get the Data Catalog updated with the correct partition information is to run MSCK REPAIR TABLE. If I do this inside the Glue ETL job using the Spark Context like so: ``` spark.sql("use gp550_load_database_beta") spark.sql("msck repair table gp550_load_table_beta").show() ``` The following happens: Serde Properties of my table are updated with “serialization.format : 1” Table Properties are updated with: EXTERNAL : TRUE spark.sql.partitionProvider : catalog ALL Data Types in my table are set to “String” with a comment of “from deserializer” Basically it makes a mess. If I instead run MSCK REPAIR TABLE from boto3, or if I manually run it from Athena Console, then there are no issues. No Serde Properties are changes, no table properties, no data types are changed, it simply adds my partitions to the Data Catalog like I want. I do like so in my ETL job: ``` client = boto3.client('athena') sql = 'MSCK REPAIR TABLE gp550_load_database_beta.gp550_load_table_beta' context = {'Database': 'gp550_load_database_beta'} client.start_query_execution(QueryString = sql, QueryExecutionContext = context, ResultConfiguration= { 'OutputLocation': 's3://aws-glue-assets-977465404123-us-east-1/temporary/' }) ``` Why does it behave so differently? Is it because somehow I need to tell Spark to work with HIVE? I had thought that since I already had a spark context it would be easy to use that to kick off the MSCK REPAIR TABLE, but obviously I was surprised at the result!

Hi Team, We have a dataset coming in from the source team which keeps on changing on a daily basis for ex: one day the dataset would have 100 columns, another day it would have 92 columns and the dataset is in csv format(tab separated). We have created a glue crawler to crawl all the files at once, the problem is we are able to load the dataset in glue Schema/table, but data within the table is misaligned meaning records are not aliigned with the column names as it should. While creating the glue crawler, I have passed below configuration: In Grouping behavior for S3 data (optional): I have check marked **Create a single schema for each S3 path ** In Configuration options (optional) During the crawler run, all schema changes are logged. When the crawler detects schema changes in the data store, how should AWS Glue handle table updates in the data catalog? -> Add new columns only. -> Update all new and existing partitions with metadata from the table. How should AWS Glue handle deleted objects in the data store? -> Mark the table as deprecated in the data catalog. Could you please help me out how to fix this misalignment problem. Thank you in advance. Regards, Apurva

I am trying to understand how Glue divides up large inputs among the worker nodes. Example: * Say I have a Glue job that will need to process 1,000,000 input records * The job is configured to use 5 workers Is Glue going to send 200,000 records to each of the 5 workers? Is Glue going to send 'X' number of records to each worker and then send another 'X' records as the workers finish? I am not so much interested in how to configure this job. I am most interested in how does Glue process? I am going to use this information in the design of my project.

Anyone can help me? Need to run my Redshift Stored Procedure, always have and error to this query. Do I have input a wrong query? Please help ``` import sys from awsglue.transforms import * from awsglue.utils import getResolvedOptions from pyspark.context import SparkContext from awsglue.context import GlueContext from awsglue.job import Job args = getResolvedOptions(sys.argv, ['JOB_NAME']) sc = SparkContext() glueContext = GlueContext(sc) spark = glueContext.spark_session logger = glueContext.get_logger() job = Job(glueContext) job.init(args['JOB_NAME'], args) # dw-poc-dev spark test source_jdbc_conf = glueContext.extract_jdbc_conf('glue_connection') from py4j.java_gateway import java_import java_import(sc._gateway.jvm,"java.sql.Connection") java_import(sc._gateway.jvm,"java.sql.DatabaseMetaData") java_import(sc._gateway.jvm,"java.sql.DriverManager") java_import(sc._gateway.jvm,"java.sql.SQLException") conn = sc._gateway.jvm.DriverManager.getConnection(source_jdbc_conf.get('url'),source_jdbc_conf.get('user','username'), source_jdbc_conf.get('password','password')) # call stored procedure rs = conn.executeQuery('call ss_schema.sp_weekly_kpi_01_bbbklg()'); conn.close() ```

Hello. I'm trying to create an analysis from my DocumentDB instance. I'm using the aws services Glue, Athena and Quicksight. In Glue I have created a connection to the DocumentDB and a crawler for auto creating tables. This works as expected and tables are created and displayed in glue. Even though I specify that the crawler should not give the tables any prefixes, it does add the database name as a prefix. When I look at the Glue catalog in Athena (the default AwsDataCatalog) I do see the database that was created in glue, however it does not show any tables. If I click on edit, it takes me to the correct database in glue which displays the tables that have been created by the previously mentioned crawler. So my first question is **Why doesn't the tables show up in Athena?** This is blocking me from performing queries in Athena. When I go to QuickSight and select the default Athena glue catalog ("AwsDataCatalog") I DO get the tables created by the crawler, and I can create datasets. However, when I try to create an analysis using these datasets, I get the error: ``` sourceErrorCode: 100071 sourceErrorMessage: [Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. HIVE_UNSUPPORTED_FORMAT: Unable to create input format ``` I have looked a bit around and some people said that this error is due to the table properties **"Input format"** and **"Output format"** being empty (which they indeed are for me). I have tried entering almost all the different formats to the table, but I keep on getting the Quicksight error above only now it has the input format at the end ``` HIVE_UNSUPPORTED_FORMAT: Unable to create input format json ``` **So my second questions is** I do not see anywhere in the crawler where I can specify input or output format. Does it have to be done manually? And What are the correct input and output formats for my setup?

Does relationalize only go one layer in depth? I have json objects that have 2 to 3 layers of struct arrays and I get this error on anything past the first array layer : An error occurred while calling o136.pyWriteDynamicFrame. Bad dbtable value expecting either schema.table or table what am I missing here? code example (works as expected with objects that have 1 layer of array depth) ``` PostgreSQLtable_node3 = glueContext.create_dynamic_frame.from_catalog(database="tekmetric",table_name="repair_orders") resolvechoice2 = ResolveChoice.apply(frame=PostgreSQLtable_node3, choice="make_struct", transformation_ctx="resolvechoice2") dropnullfields3 = DropNullFields.apply(frame=resolvechoice2, transformation_ctx="datasource0").drop_fields("jobs.parts.dotNumbers") dfc = Relationalize.apply(frame=dropnullfields3, staging_path="s3://tekmetric-temp/repair-orders/", name="repair_orders", transformation_ctx="relationalize4") for df_name in dfc.keys(): m_df = dfc.select(df_name) if m_df.count(): glueContext.write_dynamic_frame.from_jdbc_conf(frame = m_df, catalog_connection = "postgres", connection_options = {"dbtable": df_name, "database": "tekmetric"}) ```

Can we directly transfer or copy data from Sql Server on premises to Snowflake with help of AWS Glue?

Hi Team, Me trying to build Cost and usage analysis using Glue & Athena. Referring [https://wellarchitectedlabs.com/cost/200_labs/200_4_cost_and_usage_analysis/2_setup_athena/](). While previewing the table facing following error: HIVE_BAD_DATA: Not valid Parquet file: s3://aws-s3-access-inventory/NS/NS-AWS-CUR/20220201-20220301/20220221T114629Z/NS-AWS-CUR-Manifest.json expected magic number: PAR1 got: This query ran against the "cost" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 28d914a4-7640-4641-9c72-627d48cb02f0 The json file does exists in stated folder under bucket. Please assist. Regards, Nikhil Shah

Need to know if it is possible and feasible to do ETL with help of AWS Glue from SQL Server DB on premises(JDBC Connector used ) to Snowflake(JDBC Connector)?

How much costing to use SCT for SSIS Packages conversion in AWS ETL?

Curious to know about, can we use AWS Glue to migrate 24 TB data from MS SQL Server(On-prem) to S3?How much time will it take (average) and how much effective it would be from other options? And would it be costly from other options of data transfer(like AWS DataSync, Snowball)?

When developing some Glue scripts from a successful Crawler run from a JDBC Oracle data source, I am encountering an error that I cannot resolve. ``` An error occurred while calling o102.pyWriteDynamicFrame. Decimal precision 136 exceeds max precision 38 ``` I've tried specifically editing the schema in the catalog, but then printing the schema in the Glue job reveals that the changes did not actually work. I believe this is a "feature" of DynamicFrames where each row can have a slightly different schema. I've tried using SelectFields so that the offending data columns aren't included, but the job fails with the same error. I've tried using Lake Formation to generate the blueprint and workflow to see if that would use more default job options but it results in the same errors. As I have hundreds of tables where this is going to be an issue, I'm trying to find the most robust way to solve without having to manually change code in each job. Interestingly, the issue occurs only when writing to s3--the loading of the data into the DynamicFrame seems to be okay and the ApplyMapping also doesn't result in an error. It's just the last stage before job.commit() that is causing the problem. Can anyone advise a workaround for this very frustrating issue? Many thanks for reading.

AWS Glue crawler is not pulling all tables names from s3 bucket. Files are present in S3 bucket, if there any setting we have to change for crawler so it pull all the tables from s3 bucket. Is there is know issue for glue crawler to pull data from s3 bucket.

Redshift external schema won't show tables from AWS Glue. using AWS crawler tables names are pulled from S3 bucket, tables are listed in Glue - Data Catalog tables but when external schema is created using Glue Database (which is created during above process), it doesn't have any table. Below option is enabled, network traffic passes through VPC. If network traffic passes through VPC do we need to enable and VPC Redshift endpoint or glue endpoint. Enhanced VPC routing - Enabling this option forces network traffic between your cluster and data repositories through a VPC, instead of the internet.

We have Iceberg tables created in Account A, and a cross account glue catalog in Account B, permissions look correct, however when we try and query the tables from account B (queries work fine in Account A) we get: HIVE_METASTORE_ERROR: Table storage descriptor is missing SerDe info Has anyone else had luck here?

My Spark code is running extremely slow and either timing out or not running at all . I though have just 4 records in the source S3 bucket that I am trying to process. Can anyone suggest if we can increase the SPARK power in the docker to make it run faster ? I am using the AWS Glue 3 Docker instance to set up my local environment. I am using a notebook to submit the jobs and being a newbie , do not know how to change the spark-submit configuration in this env

Is glue version 3 available for Python Shell also or it will support only version 1.0?

We are going to start development of GLUE ETLs . The different options available are : 1. From the AWS Console : This seems to be costly and slow and not very efficient for developing scripts 2. From Dev Endpoints : Billing Rate is high 3. By AWS Glue Docker Image : Lacks functionality 4. Interactive Sessions 5. Local Setup Which among these will be simple to set up and not incur cost ?

I have set up my local glue development environment following instructions at [https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-libraries.html]() and its working . I am trying to extend Jupyter by installing NB Extensions as in [https://jupyter-contrib-nbextensions.readthedocs.io/en/latest/install.html]() pip command executes fine ``` pip install jupyter_contrib_nbextensions --user ``` I encounter an error on this step : ``` jupyter contrib nbextension install --user ``` `PermissionError: [Errno 13] Permission denied: '/home/glue_user/.jupyter/migrated'` I get a similar error on the Docker Log as well `[W 2022-06-01 07:43:44.083 LabApp] [Errno 13] Permission denied: '/home/glue_user/.jupyter/lab/workspaces'` Any help ? **Docker Image : **amazon/aws-glue-libs:glue_libs_3.0.0_image_01 **Docker Started using : ** ``` docker run -it -v ~/.aws:/home/glue_user/.aws -v $JUPYTER_WORKSPACE_LOCATION:/home/glue_user/workspace/jupyter_workspace/ -e AWS_PROFILE=$PROFILE_NAME -e DISABLE_SSL=true --rm -p 4040:4040 -p 18080:18080 -p 8998:8998 -p 8888:8888 --name glue_jupyter_lab amazon/aws-glue-libs:glue_libs_3.0.0_image_01 /home/glue_user/jupyter/jupyter_start.sh ```

I'm brand new to Athena & Glue. We have several sets of data in S3 in partitioned format e.g. /year=yyyy/month=mm/day=dd. Some of these data sets are incremental (as would make sense for partitions) but some are simply complete snapshots of all the data we're interested in. When creating a Glue crawler things work really nicely out of the box for the naturally incremental/partitioned data sets, and seamless tables are automatically created. For the data set that are complete snapshots, however, we end up with lots of duplicate/old data in the tables because all the "partitions" are included in the table. For the latter, is there some way to configure the glue crawler to only include the most recent partition?

Hi I'm using Athena to query AWS glue tables setup by a crawler from Dynamo DB. A Athena query to : show tables works perfect However when i do a simple select on any of the tables I get: GENERIC_INTERNAL_ERROR: java.net.URISyntaxException: Relative path in absolute URI: arn:aws:dynamodb:eu-west-1:[account]:table/[table-name] My query ID is: Query Id: 22e3a909-f7b4-41a7-9dfb-96b081f4b3b5

I I have performed some AWS Glue version 3.0 jobs testing using Docker containers as detailed [here](https://aws.amazon.com/blogs/big-data/develop-and-test-aws-glue-version-3-0-jobs-locally-using-a-docker-container/). The following code outputs two lists, one per connection, with the names of the tables in a database: ```python import boto3 db_name_s3 = "s3_connection_db" db_name_mysql = "glue_catalog_mysql_connection_db" def retrieve_tables(database_name): session = boto3.session.Session() glue_client = session.client("glue") response_get_tables = glue_client.get_tables(DatabaseName=database_name) return response_get_tables s3_tables_list = [table_dict["Name"] for table_dict in retrieve_tables(db_name_s3)["TableList"]] mysql_tables_list = [table_dict["Name"] for table_dict in retrieve_tables(db_name_mysql)["TableList"]] print(f"These are the tables from {db_name_s3} db: {s3_tables_list}\n") print(f"These are the tables from {db_name_mysql} db {mysql_tables_list}") ``` Now, I try to create a dynamic dataframe with the *from_catalog* method in this way: ```python import sys from pyspark.context import SparkContext from awsglue.context import GlueContext from awsglue.job import Job from awsglue.dynamicframe import DynamicFrame source_activities = glueContext.create_dynamic_frame.from_catalog( database = db_name, table_name =table_name ) ``` When `database="s3_connection_db"`, everything works fine, however, when I set `database="glue_catalog_mysql_connection_db"`, I get the following error: ```python Py4JJavaError: An error occurred while calling o45.getDynamicFrame. : java.lang.ClassNotFoundException: com.mysql.cj.jdbc.Driver ``` I understand the issue is related to the fact that I am trying to fetch data from a mysql table but I am not sure how to solve this. By the way, the job runs fine on the Glue console. I would really appreciate some help, thanks!

Is there a way to create a Lakeformation Resource Link to a database/table through Boto3 client. I am creating a database using glue_client.create_database() function. Now , I need to create a Lakeformation Resource Link Pointing to that database.

I'm using a AWS Glue crawler for a RDS Aurora PostgreSQL, the table contains some columns with the money datatype, the crawler create the table with those columns as double. `AWSGlueDataCatalog_node1652381535100 = glueContext.create_dynamic_frame.from_catalog( database="tmp_db", table_name="postgres_public_list", transformation_ctx="AWSGlueDataCatalog_node1652381535100",) ` But then when I start to working on the AWS Glue, any action, even a simple AWSGlueDataCatalog_node1652381535100.count() gives me this kind of error: > ... Caused by: org.postgresql.util.PSQLException: Bad value for type double : 2,400.00 ... Doesn't matter if I try to change the columns datatype to string, keeps saying the same error. Any ideas how to solve this issue?

I have an RDS PostgreSQL database that has table names and column names with upper cased characters. I have created a glue crawler that connects to the database via jdbc and populates the glue data catalog with the database schemas but in that translation the upper case characters are converted to lower case characters when stored in the data catalog tables. When I run a glue job to query I get this error: An error occurred while calling o94.getDynamicFrame. ERROR: column "id" does not exist I made a copy of this table and changed the table names and column names to have all lower case characters and the same glue jobs and queries run successfully. Changing the table names and column names to lower cased characters in our production environment is just not an option due to the extensive work it would require. I found the 'Edit Schema' option in the Glue UI where you can change column names and data types and thought for a moment the solution had been found. However, when you change a character to upper case and then select "Save" it is reverted to lower case as it is saved. I have edited the pyspark script directly and worked with the glueContext.create_dynamic_frame.from_catalog method using the additional_options parameter to build my select statement using upper and lower case characters but that still fails with the error message noted above. # Script generated for node PostgreSQL table PostgreSQLtable_node1 = glueContext.create_dynamic_frame.from_catalog( database="mydatabase", table_name="mytable", additional_options={"query":"SELECT id from mytable;"}, transformation_ctx="PostgreSQLtable_node1" I believe the failure is because the schema as it is stored in the data catalog contains lower characters while the actual schema in the database is upper characters so when Glue tries to work with the table it is looking for "id" while the actual is "ID" and so "not found" is returned. I have read about the CaseSensitive option and looking in that direction next for a solution. I have not seen any recent (less than couple years old) posts about this issue so not sure if I'm missing something. Any assistance would be greatly appreciated.

I have an event rule rule that triggers a glue job. I would like to pass information from the event details as a parameter to the Glue job. For example, say my event contains: {"details": {"database_name": "my_database"}} and and my job has a parameter --DATABASE_NAME. Is it possible to pass database_name from the event as the --DATABASE_NAME parameter in the job? I haven't been able to find anything in the documentation or online on how to do it. Thanks!

Hi, I have defined a crawler that connects to monngodb and created a datacatalog table successfully. I am trying to define an etl job that will write to aws rds instance. I am getting the following error when running the etl job: IllegalArgumentException: Missing collection name. Set via the 'spark.mongodb.input.uri' or 'spark.mongodb.input.collection' property I added a definition in the data catalog table for database and collection and yet keep getting this error. What is missing? Thanks!

Have created a DMS task to migrate data from MongoDB to S3 in parquet, and will be using parquet files in Glue. But the column names contain spaces in their names, due to which the parquet files are not readable in Glue, it is throwing error - *AnalysisException: Attribute name "column A" contains invalid character(s) among " ,;{}()\n\t=". Please use alias to rename it*. Had been renaming in the rule, but have multiple columns with this space issue. Is there a way to add a transformation rule in DMS to do this for all columns having such issue. I tried doing this in Glue code, but then unable to even read the parquet file. Tried by applying the schema after changing and reading again but it is leading to nulls in the fields. ``` `file = "/content/LOAD00000001.parquet" df = spark.read.parquet(file) newdf = spark.read.schema(df.schema).parquet(file) newdf.coalesce(1).write.format('parquet').save('/content/4/',header = 'true')` ``` Please help me find a way to handle this dynamically.

Newly created tables have stopped appearing in my Data Catalog GUI. I have created several tables via S3 crawlers to parse compressed JSON and can see the newly created tables in other services (for instance Glue Studio or Kinesis Firehose), but cannot view or edit them in the Data Catalog "Tables" menu. This started occurring after I deleted several tables via the GUI and has persisted since. Any help is appreciated!

Hi, I'd like to know is it possible to write python's functions in aws lambda to complete a aws glue job created in aws studio. How can we mix aws studio jobs and aws lambda ?

I created a Data Catalog with a table that I manually defined. I run my ETL job and all works well. I added partitions to both the table in the Data Catalog, as well as the ETL job. it creates the partitions and I see the folders being created in S3 as well. But my table data types change. I originally had: | column | data type | | --- | --- | | vid | string | | altid | string | | vtype | string | | time | timestamp | | timegmt | timestamp | | value | float | | filename | string | | year | int | | month | int | | day | int | But now after the ETL job with partitions, my table ends up like so: | column | data type | | --- | --- | | vid | string | | altid | string | | vtype | string | | time | bigint | | timegmt | bigint | | value | float | | filename | string | | year | bigint | | month | bigint | | day | bigint | Before this change of data types, I could do queries in Athena. Including a query like this: ``` SELECT * FROM "gp550-load-database"."gp550-load-table-beta" WHERE vid IN ('F_NORTH', 'F_EAST', 'F_WEST', 'F_SOUTH', 'F_SEAST') AND vtype='LOAD' AND time BETWEEN TIMESTAMP '2021-05-13 06:00:00' and TIMESTAMP '2022-05-13 06:00:00' ``` But now with the data types change, I get an error when trying to do a query like above ``` "SYNTAX_ERROR: line 1:154: Cannot check if bigint is BETWEEN timestamp and timestamp This query ran against the "gp550-load-database" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 2a5287bc-7ac2-43a8-b617-bf01c63b00d5" ``` So then if I go into the the table and change the data type back to "timestamp", I then run the query and get a different error: ``` "HIVE_PARTITION_SCHEMA_MISMATCH: There is a mismatch between the table and partition schemas. The types are incompatible and cannot be coerced. The column 'time' in table 'gp550-load-database.gp550-load-table-beta' is declared as type 'timestamp', but partition 'year=2022/month=2/day=2' declared column 'time' as type 'bigint'. This query ran against the "gp550-load-database" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: f788ea2b-e274-43fe-a3d9-22d80a2bbbab" ``` With Glue Crawlers, you can influence how the partitions are created. You can have the Crawler modify the Data Catalog table, or not make changes to the table scheme, other than adding new partitions: ``` { "Version": 1.0, "CrawlerOutput": { "Partitions": { "AddOrUpdateBehavior": "InheritFromTable" }, "Tables": {"AddOrUpdateBehavior": "MergeNewColumns" } } } ``` basically, this *InheritFromTable* behavior is what I am looking for with ETL jobs. Does anyone know what is happening?

Hi, I have set up a crawler to run against an s3 bucket and set crawler configurations as follows: Schema updates in the data store: Ignore the change and don't update the table in the data catalog, Inherit schema from table: Update all new and existing partitions with metadata from the table, Object deletion in the data store: Delete tables and partitions from the data catalog Updation of partition is taking place but deletion of partitions from catalog for the partitions deleted from s3 bucket is not taking place. It is not throwing any errors as well.

I have S3 files that are uploaded to a single bucket. There is no folders or anything like that, its just 1 file per hour uploaded to this bucket. I run a Glue ETL job on these files, do some transformations, and insert the data into a Glue Data Catalog stored in a different bucket. I can then query that Glue Data Catalog with Athena, and that works. What I would like to do is store the files in the S3 folder of the Data Catalog as YEAR/MONTH/DAY, using partitions. Even though the SOURCE data is just files uploaded every hour with no partitions, I want to store them in the Data Catalog WITH partitions. So I extracted the YEAR, MONTH, DAY from the files during Glue ETL, and created columns in my Data Catalog table accordingly and marked them as partitions: Partition 1 YEAR Partition 2 MONTH Partition 3 DAY The proper values are in these columns, and I have verified that. After creating the partitions I rand MSCK REPAIR TABLE on the table, and it came back with "Query Ok." I then ran my Glue ETL job. When I look in the S3 bucket I do not see folders created. I just see regular r-part files. When I click on the Table Schema it shows the columns YEAR, MONTH, DAY marked as partitions, but when I click on View Partitions it just shows: year month day No partitions found What do I need to do? These are just CSV files. I can't control the process that is uploading the raw data to S3, it is just going to store hourly files in a bucket. I can control the ETL job and the Data Catalog. When I try to query after creating the partitions and running MSCK REPAIR TABLE, there is no data returned. Yet I can go into the Data Catalog bucket and pull up one of the r-part files and the data is there.

Hi, I would like to know, when crawling the data from s3 in order to create a database; does the database must be a relational database ? It can have tables that no relation with other tables ?

Team, I have created AWS glue container using aws glue image . When I am logging into docker using bash command I can see aws credentials are set in docker . When I am running aws s3 ls from docker it s providing me list of s3 buckets. I have written small code to test to connection to aws but it is giving error . ``` import os import boto3 boto3.__version__ client = boto3.client('sts') response=client.get_caller_identity() ``` error I am getting is : An error was encountered: An error occurred (InvalidClientTokenId) when calling the GetCallerIdentity operation: The security token included in the request is invalid. Traceback (most recent call last): File "/usr/local/lib/python3.6/site-packages/botocore/client.py", line 314, in _api_call return self._make_api_call(operation_name, kwargs) File "/usr/local/lib/python3.6/site-packages/botocore/client.py", line 612, in _make_api_call raise error_class(parsed_response, operation_name) botocore.exceptions.ClientError: An error occurred (InvalidClientTokenId) when calling the GetCallerIdentity operation: The security token included in the request is invalid. I have tried unset the existing credentials and set new still getting same issue I am using 1.0 glue image.

LAUNCH ERROR | Error downloading from S3 for bucket: aws-glue-assets-409547615589-ap-southeast-1, key: scripts/Dos_Boardinfo.py.The specified bucket does not exist (Service: Amazon S3; Status Code: 404; Please refer logs for details.

Hello, I am using AWS Glue to read a data from S3 and write in a table in redshift. I want to run some SQL statement in my AWS glue auto generated script. I am importing the from pyspark.sql.functions import * and then using spark.sql and putting my sql inside it . spark.sql(""" update datahub_source.dos.DemographicsSCPR set ETL_CURR_REC='Y',ETL_CREATED_DT=GETDATE(),ETL_UPDATED_DT=GETDATE() WHERE ETL_CURR_REC IS NULL""" Its throwing the error below so please help as i am new to Glue and doing a POC- ParseException: "\nmismatched input 'update' expecting {'(', 'SELECT', 'FROM', 'ADD', 'DESC', 'WITH', 'VALUES', 'CREATE', 'TABLE', 'INSERT', 'DELETE', 'DESCRIBE', 'EXPLAIN', 'SHOW', 'USE', 'DROP', 'ALTER', 'MAP', 'SET', 'RESET', 'START', 'COMMIT', 'ROLLBACK', 'REDUCE', 'REFRESH', 'CLEAR', 'CACHE', 'UNCACHE', 'DFS', 'TRUNCATE', 'ANALYZE', 'LIST', 'REVOKE', 'GRANT', 'LOCK', 'UNLOCK', 'MSCK', 'EXPORT', 'IMPORT', 'LOAD'}(line 1, pos 1)\n\n== SQL ==\n update datahub_source.dos.DemographicsSCPR set ETL_CURR_REC='Y',ETL_CREATED_DT=GETDATE(),ETL_UPDATED_DT=GETDATE() WHERE ETL_CURR_REC IS NULL \n-^^^\n"

Hi I was trying to work on a simple problem where I am taking data from 3 csv files in s3(source) and after combining them, I am appending them to a table in postgre sql where my database is by establishing a connection in my glue catalog with that database. But I am facing an error stating that this table already exist and the data is not getting attached to it. Kindly help me if there is anything wrong with this approach

I am trying to combined 2 sources and added repartition(1) for a single output file in csv, the script worked until today and when I go back to check the script, it seems like Glue deleted repartition(1) part and connected automatically from Union. I added the repartition code back in and saved, but it still can't be save and resulting with 2 outputs instead of 1. Same thing happening when I try to create a new glue job.

I am not able to find the option in Glue Studio to open an existing job visually. When select edit job from the Glue Studio, it only has the option to edit the script. Does any one run into this?

I have csv data uploaded to S3 bucket, letting Glue to set them up as tables for later use. I want all the columns loaded as string without designating each column name by name. How could we configure Glue to load all columns as string, not transforming into bigint or so?

I have several scheduled Glue jobs, and now half of them have missing schedules. When I try to add a schedule back, a message in red flashes at the top of the browser, but the site refreshes to create schedule form again. How do I find out what happened to the schedules, so they are not removed again, and how to I identify what is the error when adding a schedule back to the job? Edit: Overnight, the schedule create failure has resolved itself. I was able to recreate the missing schedules. However, I need to know why the schedules were removed.

I have tried a myriad of ways to get psycopg2 imported into a script. * import psycopg2 (on it's own as psycopg2 is listed as available in Glue 3.0 when I print(help("modules")) * Downloaded both 64-bit and 32-bit and tried both in Python library path (individually) - log actually says these loaded but import still fails * Added --additional-python-modules as a parameter and tried both with and without a specific version - I think this fails on my because of network security implemented (no downloading from external sources). I will get the error "ModuleNotFoundError: No module named 'psycopg2._psycopg'" or "ModuleNotFoundError: No module named 'psycopg2'". The former is when I use the whl objects. The latter is when I try to use the module that appears to be available by default.

I'm having trouble creating iceberg tables in Athena. I keep getting "Invalid S3 request" as an error message. ``` CREATE TABLE iceberg_test ( event_date timestamp, log string ) PARTITIONED BY ( hour(event_date) ) LOCATION 's3://bucket/iceberg/test' TBLPROPERTIES ( 'table_type'='ICEBERG' ); ```

We have a usecase to read Oracle table and publish the records into AWS MSK topic. For that purpose we are using MSKConnect and trying to deploy Confluent JDBCSourceConnector. We are using AWS Glue schema registry for schema management. We have used below configuration in our connector but its just giving the error and connector always goes to failed status. ``` key.converter= org.apache.kafka.connect.storage.StringConverter key.converter.schemas.enable= false key.converter.avroRecordType= GENERIC_RECORD key.converter.region= us-east-1 key.converter.registry.name= ebx-control-tbl-registryE key.converter.schemaAutoRegistrationEnabled= true value.converter= com.amazonaws.services.schemaregistry.kafkaconnect.AWSKafkaAvroConverter value.converter.schemas.enable= true value.converter.avroRecordType= GENERIC_RECORD value.converter.region= us-east-1 value.converter.registry.name= ebx-control-tbl-registry value.converter.schemaAutoRegistrationEnabled= true ``` Its giving below error. ``` [Worker-0dc06f886ba9272ef] Caused by: org.apache.kafka.connect.errors.DataException: Converting Kafka Connect data to byte[] failed due to serialization error: ``` Has anyone successfully used Confluent JDBCSourceConnector with MSK connect and AWS Glue Schema registry?

Dear community This is happening to me when I´m running an ETL job trying to transform an Oracle database table to parquet format and store it in a S3 bucket. I´ve been checking permissions and everything that I could imagine but I´m not able to figure out where does the error comes from. I highly apreciate if anyone can give me some lights about it. Regards,

I am trying to write data from S3 to BigQuery using Glue, this is working perfectly fine -** have used the Glue BQ connector from Marketplace**. I am able to write the data to the table at BQ but when trying to use prequery/postquery to run a delete SQL query on BQ table, it does not run. How can I perform the deletion that I need to run on the table at BQ, is there any way for this, or am I doing any mistake while adding the prequery? Below is the code for writing the dynamic_frame to BQ where I am adding the prequery as option: **prequery** = f"""DELETE FROM fynd-1088.fynd_warehouse_pre.rds_subscriber_config_dms WHERE id in ({list_id})""" **preact** = False if deletedf_count == 0 else True writedf = ( glueContext.write_dynamic_frame.from_options( frame=target_df, connection_type="marketplace.spark", connection_options={ "parentProject": "parentProject", "table": "parentProject.dataset_name.table_name", "temporaryGcsBucket": "bucket_name", "connectionName": "new_bq", "**prequery**": prequery, "**addPreAction**": preact, }, transformation_ctx="writedf", ) )

Is there some documentation about ways to trigger Glue jobs, that go beyond static schedules and simple conditions as explained in https://docs.aws.amazon.com/glue/latest/dg/about-triggers.html? I heard about the possibility to trigger Glue jobs from Lambda functions, but all I can find about that is not much more sophisticated than static schedules and simple conditions. I have a pipeline of several Glue jobs that are normally run in a sequence once per week. The last glue job in this pipeline writes out a table that contains a flag, which is used to determine records, that need to be processed in a higher frequency. So I am looking for a mechanism, that processes this output table regularly and triggers that first glue job of the pipeline again more frequently in case a certain flag is set. I need to avoid, that the entire dataset is processed at this high frequency. How would this be done?

Hi, I'm searching a AWS tool to help me to move data from an database in redshift to an target database, but the target database is only accessible via API. I need to do some transformationsn in the data before to move to the database target.

I need to print my python shell glue job run id from the script itself into cloudwatch logs. But I didn’t find a way to find the current job run id, I have found a partial way using boto3 functions but its giving all glue job run ids and if we want to find out current job run id then its saying to filter with job run status, but this procedure works only there is one job run at a time, in my scenario I am expecting concurrent runs. Can you help me on how to find out the current job run id??

As per this [aws doc](https://aws.amazon.com/blogs/developer/python-support-policy-updates-for-aws-sdks-and-tools/), boto3 is stopping its support for python 3.6 from May 30, 2022. But in AWS Glue jobs - python shell, glue is supporting only python version 3.6 and its not even allowing us to update the python version. How should I handle my glue jobs now?

Please find packages at http://spark.apache.org/third-party-projects.html

I am getting issues in aws-glue-libs:glue_libs_3.0.0_image_01 image docker run -it -p 8888:8888 -p 4040:4040 -e DISABLE_SSL="true" -v C:/Docker/jupyter_workspace:**/home/glue_user/workspace/jupyter_workspace/ ** --name glue_jupyter amazon/aws-glue-libs:glue_libs_3.0.0_image_01 /home/glue_user/jupyter/jupyter_start.sh It is getting started locally but When I am trying to read the csv file stored locally it is giving error : An error was encountered: Path does not exist: file:/home/glue_user/workspace/employees.csv Traceback (most recent call last): File "/home/glue_user/spark/python/pyspark/sql/readwriter.py", line 737, in csv return self._df(self._jreader.csv(self._spark._sc._jvm.PythonUtils.toSeq(path))) File "/home/glue_user/spark/python/lib/py4j-0.10.9-src.zip/py4j/java_gateway.py", line 1305, in __call__ answer, self.gateway_client, self.target_id, self.name) File "/home/glue_user/spark/python/pyspark/sql/utils.py", line 117, in deco raise converted from None pyspark.sql.utils.AnalysisException: Path does not exist: file:/home/glue_user/workspace/employees.csv Or When I am trying to start with docker run -it -p 8888:8888 -p 4040:4040 -e DISABLE_SSL="true" -v C:/Docker/jupyter_workspace****:/home/glue_user/workspace** ** --name glue_jupyter amazon/aws-glue-libs:glue_libs_3.0.0_image_01 /home/glue_user/jupyter/jupyter_start.sh then container is not getting started getting following error : Bad config encountered during initialization: No such directory: ''/home/glue_user/workspace/jupyter_workspace''