Category Archives: AWS

Snippet Java for Redshift

With snippet Java for Redshift, you can free your mind

  1. Open JDBC Connection and Change Schema

  2. Example Copy Data Gzipped to Table with Column Names

  3. Generate Manifest File Content

Set MySQL character set and collation to UTF-8 (RDS)

These are notes for how to set UTF-8 (UTF8) collation and character set in MySQL database server. There are also notes on how to set the dfeault collation and characters set to UTF-8 on Amazon RDS MySQL database instances.

utf8_unicode_ci vs utf8_general_ci collation

In general, utf8_unicode_ci should always be used. It provides correct sorting in all languages. The utf8_general_ci collation takes shortcuts and does not sort exactly right in certain languages, and its only benefit is a tiny performance increase.


Amazon RDS Set Database Parameters

Altering MySQL database, table, and column collations to utf8_unicode_ci

How to Increase Disk Space on an Amazon EC2 Instance

While using Amazon Web Services (AWS) you may find that, when using one of the Amazon Machine Images (AMIs) provided, you may run out of disk space. There does not appear to be any way to “resize” an Elastic Block Store (EBS) volume; however, you can create a new one based on an existing snapshot and replace the current drive with a larger one. I have provided the instructions for doing so below.

  1. Log into the AWS Management Console
  2. Make sure that the Instance that you wish to change is not currently running
  3. Navigate to Elastic Block Store > Volumes
  4. Check the box next to the Volume that needs more space
  5. Click the More… drop down list and select Create Snapshot
  6. Enter a Name and a Description
  7. Navigate to Elastic Block Store > Snapshots
  8. Monitor the progress of the Snapshot for completion
  9. Once complete, navigate back to Elastic Block Store > Volumes
  10. Click Create Volume
  11. Enter the desired size for the new volume
  12. Make sure that you select the same Availability Zone for your instance
  13. Select the Snapshot that you just created
  14. Click Yes, Create
  15. Monitor the progress of the Volume for completion
  16. Once complete, Check the box next to the Volume that is currently connected to the Instance
  17. Make sure that no other Volumes are selected
  18. Click the More… drop down list and select Click the More… drop down list and select Detach Volume
  19. Uncheck the box selected and check the box next to the new Volume
  20. Click the More… drop down list and select Click the More… drop down list and select Attach Volume
  21. Select the Instance that you wish to change
  22. Change the value in Device from xvdf to /dev/sda1
  23. Click Yes, Attach
  24. Navigate to Instances and start your Instance
  25. Once you are satisfied that the volume works, feel free to delete the Snapshot and old Volume if you like
  26. With your new volume attached, start your EC2 instance back up. This will take a few minutes and when completed you should see the instance and volume be associated to each other. Also, stopping and restarting the instance will most likely cause the instance to get a new public IP address.


Only EC2-VPC instances with Elastic IP addresses retain their public IP address when they are stopped. If your instance is running in EC2-Classic, the EIP address is disassociated when the instance is stopped, and you must re-associate the EIP after restarting the instance. If your instance is not using an EIP, then you need to retrieve your new public DNS name for your instance from the Instances page of the Amazon EC2 console to connect to it.

Update multi schema on REDSHIFT by command line

I. Install PostgresSQL via

  • Remember set enviroment variable on window: PGHOST = localhost

II. Install Cygwin via

III. Using Cygwin connect to PSQL

Create file .sh run on Cygwin follow below content. Remember when you using notepad++ to create file .sh YOU SHOULD change from Windows Format to UNIX Format.

  • PGOPTIONS behaves the same as the options connection parameter. We need this param because on redshift we have many schemas so before run query we need set search_path to schemas we want to update.
  • PGPASSWORD behaves the same as the password connection parameter. Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via ps; instead consider using the ~/.pgpass file
  • -U is username of Redshift cluster
  • -d is database name of Redshift
  • -p is port of Redshift
  • -h is host of Redshift

IV. Run file sh on Cygwin