Questions tagged with AWS Glue DataBrew

I' m trying to follow a tutorial about Glue, to create a Data Lake from a Mysql Database (RDS). Unfortunately i'm stucked in some issues, and i can not proceed with my learning. I've used a Blueprint in glue to create the ETL process. This created a crawller and some jobs. The crawller seems to be ok, but the jobs no, the error is: "failed to execute with exception Internal service error: Invalid Input Provided".  Investigating one of ETL Jobs, i don't have permissions to change it, I receive the following error: "[s3.amazonaws.com] putObject: AccessDenied: Access Denied". If i browse the S3 from glue i can see the bucket, but from s3 main page i can not. Even with my root i can not change any permission:  The ETL Job error could be related with the s3 permissions error or should be other? Any tip? Thanks and best regards, Ricardolg...

Hi! I have in **DataBrew a Dataset with dynamic parameterized dataset**s (https://aws.amazon.com/es/blogs/big-data/simplify-incoming-data-ingestion-with-dynamic-parameterized-datasets-in-aws-glue-databrew/). Name of Parameter: 'MyTest'. Type: 'String'. Condition1 : is exactly 08. I need to **change the Condition1 with Powershell**. My best approach would be this but doesn't work: $MiHas=@{"Name"="MiPrueba";"Condition"="is 07"} Update-GDBDataset -Name "Test" -PathOptions_Parameter $MiHas -S3InputDefinition_Bucket "BucketName" -S3InputDefinition_Key "Key/"$MiHas['Condition'] -Format CSV -Csv_Delimiter ";" Any help with this would be very appreciated,lg...

I am trying to add csv files to RDS MySQL database and successfully created my files in Glue Databrew, crawled them in Glue, but when I run my Job in Glue Studio I get an error: "An error occurred while calling o106.pyWriteDynamicFrame. Data truncation: Data too long for column [insertcolumnname] at row 1". I recreated the files in jupyter notebook and there are no columns with len >255 and most are around len~80 characters. A job for one of my tables (a very simple one) did successfully run and is in my MySQL database, but all the other tables fail with this error. What am I missing here?lg...

I have a workflow that retrieves data and stores it on S3 bucket (Database snapshot). For the first time that the workflow ran, it created one folder for each table. So for example let's say I had table *products*, then it created a folder for that table. Now inside of this folder there will be a subfolder called **version_0**. Every time the workflow runs again, it will fetch the data, and create a version_1, version_2, so forth. On Athena, when I query this table, it will give me data from the latest version created. How can I access for example version_0 through the query? Following this [documentation](https://docs.aws.amazon.com/athena/latest/ug/lf-governed-tables-querying.html) , I tried to run the following: ``` select count(*) from products FOR SYSTEM_VERSION AS OF 0 ``` But I'm getting the following error:  To be honest don't even know if following this documentation that I sent above is the way to go or if there is some other way. I also looked into the Table properties going into AWS Glue -> Tables -> *products* table and then I see that there is a field ***TableVersion***. I can click on the "Compare Versions" button and it will give me a side by side between version 0 and version 1 (the current versions I have for now). Thankslg...

I am looking to ingest upstream files via Glue ETL and I need to match misspellings to existing, already standardized data, based on rules that I can either continually add to or train a model to learn from, to them add to my database. It is basically a continuously growing reference table(s). All of this is currently done in Excel by hand for multiple columns/fields and I need to automate it. General Example: I already have a list of known matches (e.g., "tigre" = "tiger"), so any field that has "tigre" should map to the proper spelling without any additional steps. I believe that I need to have a training step for matches that don't already exist. So when the spelling "tigerrr" comes along, I can map to "tiger" and, next time the process runs, the mapping occurs properly. I dove into DataBrew, but does not appear to be able to handle large reference tables for mapping in the recipes. It did not look like *FindMatches* in Glue Studio was quite the right tool either as it appears to focus on full record matching, not individual field matches. Any recommendations?lg...

quick sight is throwing permission denied issue even after providing access to that respective S3 bucket . Below are the steps I have followed and please find the attached screenshots for reference. 1. Created s3 buckets and placed all the tables data in parquet files 2. Created a db in AWS glue and ported tables trough crawlers 3. tables executing in athena 4. given s3 and athena permissions in quick sight and tried to access these tables using athena as datasource (https://repost.aws/media/postImages/original/IMdaIIJpDgSiK1ne3ig9G-6Q) (https://repost.aws/media/postImages/original/IMKUcDGiRFQRqHzM2W-kg5aw)lg...

I need to replicate data from Oracle database(views) to Postgres . please let me know the best to do it.I believe AWS Glue can help in replicating ETL data, If you have any documents(step by step instructions) please share with me. If you have any other tool Please share the details.lg...

Planning to use databrew for data validation. I not going to clean the data using Recipe, but just profile and write rule sets and then create a profile job to run on any new datsets. Could you please help me with the below: 1) can databrew work on streaming data? 2) can databrew profile job be run concurrently (at same time) on 2 split files(both have same structure) These are key to determine whether I can use databrew or not. Please advise. Thank you!!lg...

Hi, I am using databrew for data quality checks of S3 files. The files arrive continuously. Every time a files arrives I have an eventbridge job which triggers the databrew job. I have the below questions: 1) is there a way I can trigger the same Databrew profile job multiple times for different files(for ex: let's say we have files xyz1, xyz2, xyz3, etc). they all have same schema and should trigger the same job. 2) If two different (different schemas) files arrive (ex: xyz1, pqr1), can same profile job be configured for 2 different files? The rules for these 2 files will be different. I dont think same profile job can be used, but just thought to check. Thanks!!lg...

Using AWS Pydeequ in databricks I am performing Data Quality checks. When I run this below mentioned code it provide only metrics results as my output (like Check_level, check_status, constraint, constraint_status, constraint_message). My Question is how can I get the failed records(Bad records) put it in separate dataframe or a table along with metrics(constraint_status, constraint_message) bad data should not process further and split good record put it in separate dataframe to process further ? Source_DF: df = spark.read.parquet("s3a://amazon-reviews-pds/parquet/product_category=Electronics/") **Code:** from pydeequ.checks import * from pydeequ.verification import * check = Check(spark, CheckLevel.Warning, "Review Check") checkResult = VerificationSuite(spark) \ .onData(source) \ .addCheck( check.hasSize(lambda x: x >= 3000000) \ .hasMin("star_rating", lambda x: x == 1.0) \ .hasMax("star_rating", lambda x: x == 5.0) \ .isComplete("review_id") \ .isUnique("review_id") \ .isComplete("marketplace") \ .isContainedIn("marketplace", ["US", "UK", "DE", "JP", "FR"]) \ .isNonNegative("year")) \ .run() checkResult_df = VerificationResult.checkResultsAsDataFrame(spark, checkResult) checkResult_df.display() Please share any solution or codes to achieve this scenario. That would be helpful.lg...

Hi, Does anyone use PyDeequ for large enterprises. I am exploring this library and have the below questions: 1) Looking at the github repo it doesnt seem like it is actively udated. ALso, it supoorts Spark 3.0.0 but not later versions. 2) Some of the apis didnt work(for complex examples). I dont know if there is any Amazon support. 3) Also the scala version(deequ) is more up to date than the python version(PuDeequ). s is there a plan to sunset the PyDeequ version 4) Should I use this for large enterprise data validation framework or there are any other alternate tools. Kindly advise. Thank you!lg...

Hello All I have created 3 docker containers running in one network using docker images as follows : postgres aws glue image oracle image Sharing docker yml for same . ``` version: "2" services: spark-postgres: image: postgres:latest container_name: spark-postgres build: ./postgresql restart: always hostname: spark-postgres env_file: - ./env/postgresdb-env-vars.env ports: - "5432:5432" volumes: - ./data/territoryhub-replication/postgresql:/var/lib/postgresql/data networks: glue-network: ipv4_address: 10.4.0.4 spark-oracle: image: oracle:test container_name: spark-oracle build: ./oracle restart: always hostname: spark-oracle env_file: - ./env/oracledb-env-vars.env ports: - "1521:1521" volumes: - ./data/territoryhub-replication/oracle:/opt/oracle/oradata - ./oracle/oracle-scripts:/opt/oracle/scripts/startup networks: glue-network: ipv4_address: 10.4.0.5 spark-master: image: spark-master container_name: spark-master build: ./spark hostname: spark-master depends_on : - spark-postgres - spark-oracle ports: - "8888:8888" - "4040:4040" env_file: - ./env/spark-env-vars.env command : "/home/jupyter/jupyter_start.sh" volumes: - ../app/territoryhub-replication:/home/jupyter/jupyter_default_dir networks: glue-network: ipv4_address: 10.4.0.3 networks: glue-network: driver: bridge ipam: config: - subnet: 10.4.0.0/16 gateway: 10.4.0.1 ``` Also I would like to mention I was not finding oracle jdbc driver to connect to oracle database so in my glue image (spark-master) I have added jar file . Sharing docker file of that ``` FROM amazon/aws-glue-libs:glue_libs_1.0.0_image_01 COPY jar/ojdbc8.jar /home/spark-2.4.3-bin-spark-2.4.3-bin-hadoop2.8/jars/ojdbc8.jar RUN mkdir -p /root/.aws RUN echo "[default]\nregion=us-east-1" >> /root/.aws/config ``` Now I am simply trying to connect to oracle database created on same network from my local which I am running as follows : http://localhost:8888/tree? pyspark code is ``` from pyspark import SparkContext from awsglue.context import GlueContext glueContext = GlueContext(SparkContext.getOrCreate()) inputDF = glueContext.create_dynamic_frame_from_options(connection_type = "oracle", connection_options = {"url": "jdbc:oracle:thin:@//10.4.0.5:1521:ORCLCDB", "user": 'system', "password": '<some pwd>' ,"dbtable": "<some table name>"}) inputDF.toDF().show() ``` I am getting below error Invalid Oracle URL specified ``` An error was encountered: An error occurred while calling o303.getDynamicFrame. : java.sql.SQLException: Invalid Oracle URL specified at oracle.jdbc.driver.PhysicalConnection.parseUrl(PhysicalConnection.java:1738) at oracle.jdbc.driver.PhysicalConnection.readConnectionProperties(PhysicalConnection.java:1419) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:943) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:928) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:557) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:68) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:732) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:648) at com.amazonaws.services.glue.util.JDBCWrapper$$anonfun$8.apply(JDBCUtils.scala:900) at com.amazonaws.services.glue.util.JDBCWrapper$$anonfun$8.apply(JDBCUtils.scala:896) at com.amazonaws.services.glue.util.JDBCWrapper$$anonfun$connectWithSSLAttempt$1$$anonfun$apply$6.apply(JDBCUtils.scala:852) at scala.Option.getOrElse(Option.scala:121) at com.amazonaws.services.glue.util.JDBCWrapper$$anonfun$connectWithSSLAttempt$1.apply(JDBCUtils.scala:852) at scala.Option.getOrElse(Option.scala:121) at com.amazonaws.services.glue.util.JDBCWrapper$.connectWithSSLAttempt(JDBCUtils.scala:852) at com.amazonaws.services.glue.util.JDBCWrapper$.connectionProperties(JDBCUtils.scala:895) at com.amazonaws.services.glue.util.JDBCWrapper.connectionProperties$lzycompute(JDBCUtils.scala:671) at com.amazonaws.services.glue.util.JDBCWrapper.connectionProperties(JDBCUtils.scala:671) at com.amazonaws.services.glue.util.JDBCWrapper.tableDF(JDBCUtils.scala:797) at com.amazonaws.services.glue.util.NoCondition$.tableDF(JDBCUtils.scala:85) at com.amazonaws.services.glue.util.NoJDBCPartitioner$.tableDF(JDBCUtils.scala:124) at com.amazonaws.services.glue.JDBCDataSource.getDynamicFrame(DataSource.scala:863) at com.amazonaws.services.glue.DataSource$class.getDynamicFrame(DataSource.scala:97) at com.amazonaws.services.glue.SparkSQLDataSource.getDynamicFrame(DataSource.scala:683) 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) Traceback (most recent call last): File "/home/aws-glue-libs/awsglue.zip/awsglue/context.py", line 204, in create_dynamic_frame_from_options return source.getFrame(**kwargs) File "/home/aws-glue-libs/awsglue.zip/awsglue/data_source.py", line 36, in getFrame jframe = self._jsource.getDynamicFrame() File "/home/spark-2.4.3-bin-spark-2.4.3-bin-hadoop2.8/python/lib/py4j-0.10.7-src.zip/py4j/java_gateway.py", line 1257, in __call__ answer, self.gateway_client, self.target_id, self.name) File "/home/spark-2.4.3-bin-spark-2.4.3-bin-hadoop2.8/python/lib/pyspark.zip/pyspark/sql/utils.py", line 63, in deco return f(*a, **kw) File "/home/spark-2.4.3-bin-spark-2.4.3-bin-hadoop2.8/python/lib/py4j-0.10.7-src.zip/py4j/protocol.py", line 328, in get_return_value format(target_id, ".", name), value) py4j.protocol.Py4JJavaError: An error occurred while calling o303.getDynamicFrame. : java.sql.SQLException: Invalid Oracle URL specified at oracle.jdbc.driver.PhysicalConnection.parseUrl(PhysicalConnection.java:1738) at oracle.jdbc.driver.PhysicalConnection.readConnectionProperties(PhysicalConnection.java:1419) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:943) at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:928) at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:557) at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:68) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:732) at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:648) at com.amazonaws.services.glue.util.JDBCWrapper$$anonfun$8.apply(JDBCUtils.scala:900) at com.amazonaws.services.glue.util.JDBCWrapper$$anonfun$8.apply(JDBCUtils.scala:896) at com.amazonaws.services.glue.util.JDBCWrapper$$anonfun$connectWithSSLAttempt$1$$anonfun$apply$6.apply(JDBCUtils.scala:852) at scala.Option.getOrElse(Option.scala:121) at com.amazonaws.services.glue.util.JDBCWrapper$$anonfun$connectWithSSLAttempt$1.apply(JDBCUtils.scala:852) at scala.Option.getOrElse(Option.scala:121) at com.amazonaws.services.glue.util.JDBCWrapper$.connectWithSSLAttempt(JDBCUtils.scala:852) at com.amazonaws.services.glue.util.JDBCWrapper$.connectionProperties(JDBCUtils.scala:895) at com.amazonaws.services.glue.util.JDBCWrapper.connectionProperties$lzycompute(JDBCUtils.scala:671) at com.amazonaws.services.glue.util.JDBCWrapper.connectionProperties(JDBCUtils.scala:671) at com.amazonaws.services.glue.util.JDBCWrapper.tableDF(JDBCUtils.scala:797) at com.amazonaws.services.glue.util.NoCondition$.tableDF(JDBCUtils.scala:85) at com.amazonaws.services.glue.util.NoJDBCPartitioner$.tableDF(JDBCUtils.scala:124) at com.amazonaws.services.glue.JDBCDataSource.getDynamicFrame(DataSource.scala:863) at com.amazonaws.services.glue.DataSource$class.getDynamicFrame(DataSource.scala:97) at com.amazonaws.services.glue.SparkSQLDataSource.getDynamicFrame(DataSource.scala:683) 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) ```lg...