AWS Redshift Athena external table set up

Assume you’ve got S3 bucket and Redshift cluster running in EU (Frankfurt).

Set up IAM role, e.g. s3acces with the following policies
aws2

Make sure you:

  • Enabled IAM role for your Redshift cluster
    aws3
  • Activated Security Token Service Regions
    aws1
    Otherwise you’ll get

    [2019-09-18 17:38:35] [42804][500310] [Amazon](500310) Invalid operation: User arn:aws:redshift:eu-central-1:${account_number}:${user}:${db}/${db_user} is not authorized to assume IAM Role arn:aws:iam::${account}:role/s3acces;

    More detail can be found at http://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_temp_enable-regions.html

Navigate to Athena query editor https://eu-central-1.console.aws.amazon.com/athena/home?region=eu-central-1#query and create a database that points to your S3 bucket:

CREATE DATABASE IF NOT EXISTS athenadb
  LOCATION 's3://${your_backet_name}';

After that you can create an external schema and tables:

create external schema if not exists athena_test
from
database 'athenadb'
iam_role 'arn:aws:iam::${role_id}:role/s3acces'
;
create external table athena_test.test(
  venueid    integer,
  venuename  varchar(200),
  venuecity  varchar(200),
  venuestate varchar(200),
  venueseats integer
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties (
  'serialization.format' = ',',
  'field.delim' = ',',
  'input.regex' = 'ff'
)
stored as textfile
location 's3://${your_bucket}/data/'
table properties  ('has_encrypted_data'='false')
;

Changing the character set for Sybase ASE 16

If you need to change char set you can invoke the following commands on your server
1. Set LD_LIBRARY_PATH environment variable. Otherwise you’ll get

ct_connect(): protocol specific layer: internal Client Library error: There is a tds login error. The installed encryption handler returned a status that was not CS_SUCCEED

export LD_LIBRARY_PATH=/opt/sybase/OCS-16_0/lib3p64/

2. Invoke charset utility with

/opt/sybase/ASE-16_0/bin//charset -U${ASE_ADMIN} -P${ASE_ADMIN_PWD} -S${ASE_SERVER} binary.srt utf8

3. Invoke isql utility with

/opt/sybase/OCS-16_0/bin/isql -Usa -PmyPassword -SMYSYBASE
>sp_configure 'default sortorder id', 50, 'utf8'
>go

4. Restart Sybase ASE server

Before:

After:

For more details refer to Sybase ASE 16 documentation

ProxySQL query_cache_size issue with MySQL 8.0.3+ in backend

If one try to connect to ProxySQL via JDBC 8.0.X he gets error

com.mysql.cj.exceptions.CJException: Unknown system variable ‘query_cache_size’

MySQL Connector/J source code shows


if (!versionMeetsMinimum(8, 0, 3)) {
queryBuf.append(", @@query_cache_size AS query_cache_size");
queryBuf.append(", @@query_cache_type AS query_cache_type");
}

and ProxySQL are treated as MySQL 5.5.30

proxy

So for now a workaround is:

update global_variables set variable_value="8.0.4 (ProxySQL)" where variable_name='mysql-server_version';
load mysql variables to run;save mysql variables to disk;

ProxySQL GitHub threads: