Presto connectivity to Redshift

This post helps you in adding redshift connector to presto so that you can directly access redshift using Presto.

Majority of the data resides on Redshift and our end users can only access Presto. So, tried on adding redshift connector to presto. By following below steps:

1. Add redshift properties under /etc/presto/conf/catalog/ (This will add redshift catalog)

========= connector.name=redshift connection-url=jdbc:postgresql://<redshift endpoint url>:<redshift port>/<databasename> connection-user=<username> connection-password=<user password> =========

2. Now, go to presto-CLI: to find redshift catalog as well

presto> show catalogs;

Catalog

----------

hive

redshift

system

(3 rows)

Query 20190724_115553_00001_kcxs8, FINISHED, 2 nodes

Splits: 36 total, 36 done (100.00%)

0:00 [0 rows, 0B] [0 rows/s, 0B/s]

NOTE:

- Restart presto-server if you cannot find redshift catalog

- Better to add these properties on all master and all the slave nodes

3. But, while trying to access schemas from redshift, I'm getting connection attempt failed error. Maybe because of the security group issues.

presto> show schemas from redshift;

Query 20190724_115839_00002_kcxs8 failed: The connection attempt failed.

The issue is with the jdbc url, added to the redshift properties file. After fixing it, it worked fine.

Comments

AWS Connect: Reporting and Visualizations

Amazon connect offers: - built in reports i.e., historical and real-time reports. We can customize these reports, schedule them and can integrate with any BI tool of our requirement to query and view the connect data. Sample solution provided by AWS: 1. Make sure Connect is exporting the CTR data using Kinesis Data Stream 2. Use Kinesis Firehose to deliver the CTR that are in KDS to S3. (CTR's can be delivered as batch of records, so one s3 object might have multiple CTR's). AWS Lambda is used to add a new line character to each record, which makes object easier to parse. 3. s3 Event Notifications are used to send an event to modify the CTR record and saves it in S3. 4. Athena queries the modified CTR's using SQL. Use partitions to restrict the amount of data scanned by each query, improving performance and reducing cost. Lambda function is used to maintain the partitions. 5. Quicksight is used to visualize the modified CTRs. Solution variations: Convert re...

Databricks: Job aborted due to stage failure. Total size of serialized results is bigger that spark driver memory.

While running a databricks job, especially running a job with large datasets and longer running queries that creates a lot of temp space - we might be facing below issue if we have a minimal configuration set to the cluster. The simple way to fix this would be changing the spark driver config in the databricks cluster tab spark.driver.maxResultSize = 100G (change the GB based on your cluster size)

Terraform lifecycle

If we are using terraform, terraform state file is the heart of all the infrastructure that we spin up using terraform templates. There are several ways to deploy the infrastructure using terraform: 1. Using CLI (setup terraform and then run terraform commands) 2. Automated Build (terraform scripts integrated as part of your jenkins pipeline) No matter of the way we chose, we must make sure that we are using the same terraform state file, so that we are having a sync and proper checklists of the resources that we used. I would like to share the terraform commands that we do on a daily basis: terraform init = the basic/starting command which initializes the terraform (make sure the proper provider is provided. In my case, I use AWS). terraform workspace select <workspace name > (creates a new workspace, useful in scenarios where we have different terraform modules - database, servers, logs, storage) terraform state list = shows the list of terraform resour...

soletechie

Search This Blog