soletechie

All Redshift monitoring queries in one place. NOTE: All queries are provided by Redshift Documentation provided by AWS. Here is the link for all the system tables: https://docs.aws.amazon.com/redshift/latest/dg/cm_chap_system-tables.html And the Redshift Utility information: https://github.com/awslabs/amazon-redshift-utils --reason why s3 copy failed select d.query, substring (d.filename, 14 , 20 ), d.line_number as line , substring (d.value, 1 , 16 ) as value, substring (le.err_reason, 1 , 48 ) as err_reason from stl_loaderror_detail d, stl_load_errors le where d.query = le.query and d.query = <queryID> ; --copy performance information from past 7 days select q.starttime,   s.query, substring (q.querytxt, 1 , 120 ) as querytxt,        s.n_files, size_mb, s.time_seconds,        s.size_mb/ decode (s.time_seconds, 0 , 1 ,s.time_seconds)   as mb_per_s from ( select query,...

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-serv...

We use s3 COPY command to move data from S3 to Redshift table. Make sure the schema for Redshift table is created before running your COPY command. I'm writing this post to log all errors related to the COPY command that I faced which might help others in saving their time. Copy command that I have used for a while: copy <redshift table name> from '<s3 file location>' iam_role '<iam role arn>' NULL AS 'NULL' EMPTYASNULL delimiter ',' IGNOREHEADER 1;; Problem 1: Faced issue with delimiters on CSV file . As we know the delimiter for any CSV file will be comma separators, but the problem on keeping this as a delimiter is that it will fail with below error if the data also have commas in it. Error Code:  1213, Error Description: Missing data for not-null field AWS Documentation link that helped in solving this issue: https://docs.aws.amazon.com/redshift/latest/dg/r_COPY_command_examples.html#load-from-csv Solution 1.1...