Questions tagged with AWS Glue DataBrew

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

Hello Team , I have created Docker compose file mentioned below : ``` version: "2" services: spark: image: glue/spark:latest container_name: spark ** build: ./spark** hostname: spark ports: - "8888:8888" - "4040:4040" entrypoint : sh command : -c "/home/glue_user/jupyter/jupyter_start.sh" volumes: - ../app/territoryhub-replication:/home/glue_user/workspace/jupyter_workspace ``` Docker file which is getting called in build section is as follows : ``` FROM amazon/aws-glue-libs:glue_libs_3.0.0_image_01 USER root RUN mkdir -p /root/.aws RUN echo "[default]\nregion=us-east-1" >> /root/.aws/config ``` My Docker is getting started but failing (sharing logs ) Starting Jupyter with SSL /home/glue_user/jupyter/jupyter_start.sh: line 4: livy-server: command not found [I 2022-05-12 15:41:33.032 ServerApp] jupyterlab | extension was successfully linked. [I 2022-05-12 15:41:33.044 ServerApp] nbclassic | extension was successfully linked. [I 2022-05-12 15:41:33.046 ServerApp] Writing Jupyter server cookie secret to /root/.local/share/jupyter/runtime/jupyter_cookie_secret [I 2022-05-12 15:41:33.541 ServerApp] sparkmagic | extension was found and enabled by notebook_shim. Consider moving the extension to Jupyter Server's extension paths. [I 2022-05-12 15:41:33.541 ServerApp] sparkmagic | extension was successfully linked. [I 2022-05-12 15:41:33.541 ServerApp] notebook_shim | extension was successfully linked. [W 2022-05-12 15:41:33.556 ServerApp] All authentication is disabled. Anyone who can connect to this server will be able to run code. [I 2022-05-12 15:41:33.558 ServerApp] notebook_shim | extension was successfully loaded. [I 2022-05-12 15:41:33.560 LabApp] JupyterLab extension loaded from /usr/local/lib/python3.7/site-packages/jupyterlab [I 2022-05-12 15:41:33.560 LabApp] JupyterLab application directory is /usr/local/share/jupyter/lab [I 2022-05-12 15:41:33.565 ServerApp] jupyterlab | extension was successfully loaded. [I 2022-05-12 15:41:33.569 ServerApp] nbclassic | extension was successfully loaded. [I 2022-05-12 15:41:33.569 ServerApp] sparkmagic extension enabled! [I 2022-05-12 15:41:33.569 ServerApp] sparkmagic | extension was successfully loaded. Traceback (most recent call last): File "/usr/local/bin/jupyter-lab", line 8, in <module> sys.exit(main()) File "/usr/local/lib/python3.7/site-packages/jupyter_server/extension/application.py", line 584, in launch_instance serverapp = cls.initialize_server(argv=args) File "/usr/local/lib/python3.7/site-packages/jupyter_server/extension/application.py", line 557, in initialize_server find_extensions=find_extensions, File "/usr/local/lib/python3.7/site-packages/traitlets/config/application.py", line 88, in inner return method(app, *args, **kwargs) File "/usr/local/lib/python3.7/site-packages/jupyter_server/serverapp.py", line 2421, in initialize self.init_httpserver() File "/usr/local/lib/python3.7/site-packages/jupyter_server/serverapp.py", line 2251, in init_httpserver max_buffer_size=self.max_buffer_size, File "/usr/local/lib64/python3.7/site-packages/tornado/util.py", line 288, in __new__ instance.initialize(*args, **init_kwargs) File "/usr/local/lib64/python3.7/site-packages/tornado/httpserver.py", line 191, in initialize read_chunk_size=chunk_size, File "/usr/local/lib64/python3.7/site-packages/tornado/tcpserver.py", line 134, in __init__ 'certfile "%s" does not exist' % self.ssl_options["certfile"] **ValueError: certfile "/home/glue_user/.certs/my_key_store.pem" does not exist** Please help in resolving this asap Many Thanks

I am trying to implement a data quality framework for an application which ingests data from various systems(batch, near real time, real time). Few items that I want to highlight here are: * The data pipelines widely vary and ingest very high volumes of data. They are developed using spark,python,emr clusters, kafka, Kinesis stream * Any new system that we onboard in the framework, it should be easily able to include the data quality checks with minimal coding. so some sort of metadata framework might help for ex: storing the business rules in dynamodb which can automatically run check different feeders/new data pipeline created. * Our tech stack includes AWS,Python,Spark, Java, so kindly advise related services(AWS Databrew, PyDeequ, Greatexpectations libraries, various lambda event driven services are some I want to focus) * I am also looking for some sort of audit, balance and control mechanism. Auditing the source data, balancing # of records between 2 points and have some automated mechanism to remediate(control) them. * I am looking for testing frameworks for the different data pipelines. Also for data profiling, kindly advise tools/libraries, Aws data brew, Pandas are some I am exploring. I know there wont be one specific solution, and hence appreciate all and any different ideas. A flow diagram with Audit, balance and control with automated data validation and testing mechanism for data pipelines can be very helpful.

Hi, This is question with Databrew data profile capability. I am looking for record-level tracking per rule validation status. This will provide while rules has failed on a record from the dataset. At least, I am looking to separate records after data profiling, valid vs invalid records. Thanks,

Hi All, I want to make this transformation : columnA, columnB, columnC to Struct {columnA:"",columnB:"", columnC:""} to Array [{columnA:"",columnB:"", columnC:""}] The first two step works, but I don't know how to transform from step 2 to 3, i.e struct to array Someone got a solution ? thanks in advance !

Is there a way to filter a column in DataBrew using the contains operator in a case where you would like to filter by more than one word? I would like to filter an address column that contains the words Miami Beach. I can do a contains filter by the first word or the second but not both, it seems?

I failed to connect to the RDS postgreSQL database using glue, and failed to return the following message: Check that your connection definition references your JDBC database with correct URL syntax, username, and password. The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver. Exiting with error code 30 My connection steps: type JDBC JDBC URL jdbc:postgresql://xxx.xxx.us-west-2.rds.amazonaws.com:5432/xxx VPC ID vpc-xxx Subnet xxx Security group sg-xxx SSL connection required false I have checked the pg database for the above configuration and there should be no problem. My glue IAM permissions： { "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": "rds:*", "Resource": "*", "Condition": { "BoolIfExists": { "aws:MultiFactorAuthPresent": "true" } } }, { "Sid": "VisualEditor1", "Effect": "Allow", "Action": [ "s3:GetAccessPoint", "ec2:DescribeAddresses", "ec2:DescribeByoipCidrs", "s3:GetBucketPolicy", "glue:*", "kms:*", "s3:GetAccessPointPolicyStatus", "s3:GetBucketPolicyStatus", "s3:GetBucketPublicAccessBlock", "s3:GetMultiRegionAccessPointPolicyStatus", "rds-db:*", "s3:GetMultiRegionAccessPointPolicy", "s3:ListAccessPoints", "s3:GetMultiRegionAccessPoint", "rds-data:*", "s3:ListMultiRegionAccessPoints", "s3:GetBucketAcl", "s3:DescribeMultiRegionAccessPointOperation", "s3:PutObject", "s3:GetObject", "s3:GetAccountPublicAccessBlock", "s3:ListAllMyBuckets", "ec2:DescribeVpcs", "ec2:DescribeVpcEndpoints", "s3:GetBucketLocation", "s3:GetAccessPointPolicy" ], "Resource": "*" } ] } The error message gives three possible directions: 1 Check that your connection definition references your JDBC database with correct URL syntax, username, and password. I don't think this should be wrong 2 The authentication type 10 is not supported. I'm not exactly sure what this error means, and all my Google queries are to modify pg_hba.conf. However, RDS does not provide modifications to this file. 3 Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver. I don't understand what this mistake means

I have a AWS Glue customer doing POC and found that each output job creates a separate output csv file. They want to be able to create a single csv output file combining multiple input jobs or files. Any Guidance?

I'm using a Table resource to create a Glue Table like so: ``` this.glueTable = new Table(scope, `${props.glueTableName}`, { database: this.glueDatabase, tableName: props.glueTableName, bucket: props.bucket, partitionKeys: props.partitionKeys, dataFormat: props.dataFormat, columns: props.columns }); ``` I need to explicitly set the `compressionType` of this table to `"none"`. I see there is a way to do it via the console, where we go to `Edit Table`, and then fill in the compressionType under `Table properties`. Can anyone point me to the prop to use if I want to do it using cdk?

I am trying to create a connection from AWS Glue to MongoDB, but when I test the connection it fails with error: "Check that your connection definition references your Mongo database with correct URL syntax, username, and password. Exiting with error code 30". I know that my connection parameters are correct because I can connect with the same host, port, database, user name, and password from another client application (Mongo Compass). And I know that my VPC configuration should be correct too because I have another connection in the Glue, to connect to the PostgreSQL database on-premise with public IP, that works just fine. I need to use this connection in my ETL job.Can anyone help me to connect MongoDB from Glue.

Hi All, I'm looking for a Test Data management (TDM) tool in AWS which can perform below requirements, 1. TDM tool to Connect Production S3 bucket to extract files for anonymization and load it in Test S3 bucket 2. Job scheduled on daily basis to anonymize files in Prod S3 and store files in Test S3 bucket 3. Identify PII columns from S3 file and anonymize it, later these files are loaded in redshift database 4. Data integrity should be maintained between file and database, for example, incremental daily data should able to match the existing mocked PII columns in the redshift database Kindly let me know how can I achieve above requirements using AWS services Thanks & Regards, Aflah

Hello, I'm trying to import datasets in DataBrew from a PostgreSQL db. Some tables have Case Sensitive names, others don't. When I try to import Tables with only lowercase letters in their names, everything works well and I'm able to use the dataset. However when the table name includes both lower and upper case letters, the data access fails with the following error : > An error occurred while calling o106.count. ERROR: relation "genphensql.sequencingdata" does not exist Indeed the table is registered in glue as genphensql.sequencingdata. However the real table name is "GenPhenSQL"."SequencingData". In Glue, all my ETL scripts work for feeding data into my PostgreSQL database. However, as DataBrew uses the table name stored by Glue to try and access the table, this fails. Is there any plan to resolve this problem at the moment from the Glue/DataBrew teams? I think it's a recurrent problem as I have seen other related questions to upper/lowercase table names Thanks a lot for your help !

I've glue catalog and use s3 as DB. Datatype of columns are numeric in Redshift which i'm casting as decimal in Glue. But getting issue while querying the data in Athena. Column in redshift looks like (-12.887686) with data type Number. we cannot change the data type in source table

One thing I want to do is be able to "group" steps of a recipe together, in the interest of maintainability. Often times I will need to go back and tweak a recipe and it is very hard to tell which steps can logically be grouped together (i.e. they're trying to solve the same goal). One solution off the top of my head would be to be able to tag each step in a recipe. Is there any way to do this? For example, let's say that I have 5 logical steps in my preprocessing. Where step 1 could be "Filter out all purchases in region X with amount over $100" Doing this step alone in a recipe can take say 4 sub-steps. Now once I have completed creating the whole preprocessing recipe for all 5 logical steps, I can have 20+ steps in the recipe. By just looking at the recipe alone, it will be very hard to know which recipe steps are working together to achieve the same goal. My workaround right now is to just add it as documentation when I publish a recipe. For example I'd write: Step 1: Filter out purchases in region X with amt > $100 -- recipe steps 1 - 4 Step 2: Blah Blah Blah -- recipe steps 5 - 11 Step 3: Blah Blah Blah -- recipe steps 12 - 20 Now when I go back and have to edit something like changing the region of interest from region_X to region_Y for filtering, i know where to look. Now is there a better way to group steps together?

It seems like the only option when adding a new step to a recipe with the visual UI is to add it to the end, as the last step. There are cases where I'd like to modify a recipe and add a step between existing steps. The workaround I have found is to add whatever step i wanted to insert at the end, download the recipe as a JSON file, reorder the steps, then re-upload the recipe. This is a bit of a pain though. Is there any way to do this w/o that workaround? ^ Or are there any plans to add in this functionality?

When you create a job you can associate it with a project OR specifically enter the dataset and recipe to be used. Say we have Project_A that uses Recipe_A and Dataset_A. What would be the difference of creating a job by using Project_A vs specifying Recipe_A + Dataset_A. Would they be identical?

Can Databrew set a column _afternoon_ to be _1_ if _hour>12 and hour <16_ , otherwise _0_ ? This requires 1. Numerical comparison "less than" (not string comparison) 2. Boolean _and_ This actually can be done with Databrew with some arithmetic transformations and the _sign_ function, but that gets complicated. Does Databrew support _afternoon = (hour>12 and hour <16)_ ?