Instantly share code, notes, and snippets.

@ufuk
ufuk / import-csv-file-from-s3-into-aws-redshift.sql
Created Jan 31, 2019
Import CSV file from S3 into AWS Redshift
View import-csv-file-from-s3-into-aws-redshift.sql
-- Before importing, you need to create table
CREATE TABLE example_table
(
...
);
-- Importing...
COPY example_table
FROM 's3://<BUCKET_NAME>/.../example_table.csv'
CREDENTIALS 'aws_access_key_id=...;aws_secret_access_key=...'
@ufuk
ufuk / bash-echo-time-by-timezone.sh
Created Jan 31, 2019
Echo time by timezone in BASH
View bash-echo-time-by-timezone.sh
echo "$(TZ='Europe/Moscow' date +%Y-%m-%d\ %H\:%M)"
# output => 2019-01-31 16:52
@ufuk
ufuk / export-database-tables-to-s3-as-csv-file.sh
Last active Jan 31, 2019
BASH script to export some tables from the PostgreSQL database to S3 as CSV files.
View export-database-tables-to-s3-as-csv-file.sh
#!/bin/bash
export AWS_ACCESS_KEY_ID=...
export AWS_SECRET_ACCESS_KEY=...
export AWS_DEFAULT_REGION=...
PG_HOST=...
PG_USER=...
PG_PASS='...'
PG_DB=...
@ufuk
ufuk / posgresql-lightspeed-alter-table.sql
Last active Nov 5, 2018
Increase performance of alter tables or any other expensive operations by increasing WORK_MEM parameter for current session in PostgreSQL
View posgresql-lightspeed-alter-table.sql
BEGIN;
LOCK TABLE ... IN SHARE MODE;
SET LOCAL WORK_MEM = '256MB';
ALTER TABLE ...;
COMMIT;
@ufuk
ufuk / TurkishNumberUtils.java
Last active Jun 5, 2018
Utils for converting numbers to Turkish words
View TurkishNumberUtils.java
public final class TurkishNumberUtils {
private static final String SPACE = " ";
private static final String EMPTY = "";
private static final String[] PERIOD_NAMES = {EMPTY, "bin", "milyon", "milyar", "trilyon", "katrilyon", "kentilyon"};
private static final String[] UNITS_TEXTS = {EMPTY, "bir", "iki", "üç", "dört", "beş", "altı", "yedi", "sekiz", "dokuz"};
@ufuk
ufuk / learn-which-process-has-allocated-the-tcp-port.sh
Last active Mar 23, 2018
Learn which process has allocated the TCP port (for example "8080")
View learn-which-process-has-allocated-the-tcp-port.sh
lsof -i tcp:8080
# Example output:
# COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
# java 86935 ...
@ufuk
ufuk / postgres-create-drop-index-without-locking.sql
Last active Mar 10, 2018 — forked from okanmenevseoglu/postgres-create-drop-index-online.sql
Create/Drop a database index without locking the table's selects, inserts, etc. on PostgreSQL
View postgres-create-drop-index-without-locking.sql
CREATE INDEX CONCURRENTLY your_index_name on your_table_name (your_column_name);
DROP INDEX CONCURRENTLY your_index_name;
@ufuk
ufuk / postgresql-cryptography-functions.sql
Last active Jan 29, 2018
Enable cryptography functions (pgcrypto extension) in PostgreSQL.
View postgresql-cryptography-functions.sql
CREATE EXTENSION pgcrypto;
-- Examples
SELECT ENCODE(DIGEST('password', 'md5'), 'hex');
SELECT ENCODE(DIGEST('password', 'sha1'), 'hex');
SELECT ENCODE(DIGEST('password', 'sha224'), 'hex');
SELECT ENCODE(DIGEST('password', 'sha256'), 'hex');
SELECT ENCODE(DIGEST('password', 'sha384'), 'hex');
SELECT ENCODE(DIGEST('password', 'sha512'), 'base64');
@ufuk
ufuk / ScaleAndTranslateIntoRange.java
Created Jul 25, 2017
Scales and translates a value (x) into a new range [a, b].
View ScaleAndTranslateIntoRange.java
/**
* Scales and translates a value (x) into a new range [a, b].
*
* Formula:
* f(x) = a + (b - a)(x - min)/(max - min)
*
* See also: https://stackoverflow.com/a/5295202
*/
public BigDecimal scaleAndTranslateIntoRange(BigDecimal value, BigDecimal actualMin, BigDecimal actualMax, BigDecimal desiredMin, BigDecimal desiredMax) {
return desiredMax.subtract(desiredMin).multiply(value.subtract(actualMin))
@ufuk
ufuk / postgresql-export-import-schema.sh
Last active Jun 11, 2018
Exporting and then importing full schema for PostgreSQL.
View postgresql-export-import-schema.sh
# export
PGPASSWORD="<PASSWORD>" pg_dump -U <USERNAME> -h <HOST> -d <DB_NAME> -n <SCHEMA_NAME> > export.sql
# import
PGPASSWORD="<PASSWORD>" psql -U <USERNAME> -h <HOST> -d <DB_NAME> -n <SCHEMA_NAME> -f export.sql