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

Make sure you:

• Enabled IAM role for your Redshift cluster
  
• Activated Security Token Service Regions
  
  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

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:

• 1671
• 1974

DB: integration tests automation process

Windows: open file for writing (lock file)

Sometimes you need to lock file and investigate how an application under the test will be working.

There are few steps to implement it:

1. Open PowerShell console
2. Write the following code:

   $file=[System.io.File]::Open('full/path/to/your/file', 'append', 'Write', 'None')
   

3. Your test steps…
4. Don’t forget to close the file after testing by

   $file.Close()
   

P.S.: It’s possible that it’ll be the error in your application log:

Exception calling "Open" with "4" argument(s): "The process cannot access the file 'full/path/to/your/file'' because it is being used by another process."

Jenkins: changed workspace directory locations

Recently found out that workspace dir in latest Jenkins versions is changed.
For example, on Centos $WORKSPACE changed from /var/lib/jenkins/jobs/<job_name>/workspace to /var/lib/jenkins/workspace/<job_name>

Keep it in mind if you’re working with custom workspaces.

UPD: Found the same info in blog http://ingorichter.blogspot.ru/2012/02/jenkins-change-workspaces-and-build.html