Skip to content

Instantly share code, notes, and snippets.

View kovid-r's full-sized avatar
🏠
Working from home

Kovid Rathee kovid-r

🏠
Working from home
View GitHub Profile
DROP TABLE if exists d_date;
CREATE TABLE d_date
(
date_dim_id INT NOT NULL,
date_actual DATE NOT NULL,
epoch BIGINT NOT NULL,
day_suffix VARCHAR(4) NOT NULL,
day_name VARCHAR(9) NOT NULL,
day_of_week INT NOT NULL,
@kovid-r
kovid-r / get-medium-stats.js
Created January 9, 2022 13:36 — forked from igeligel/get-medium-stats.js
medium-get-totals
const totalTypes = {
VIEWS: 2,
READS: 3,
FANS: 5
};
const getTotal = tableColumn =>
[
...document.querySelectorAll(
`td:nth-child(${tableColumn}) > span.sortableTable-number`
@kovid-r
kovid-r / mysql-datahub-user.sql
Created October 18, 2021 11:03
User permissions for DataHub to connect to MySQL and fetch metadata.
CREATE USER 'datahub'@'%' IDENTIFIED BY PASSWORD 'datahub';
GRANT USAGE ON *.* TO 'datahub'@'%';
GRANT SELECT ON 'mediawiki'.* TO 'datahub'@'%';
@kovid-r
kovid-r / mysql-datahub.yml
Created October 18, 2021 10:59
Metadata Source configuration YAML file for adding MySQL to DataHub
source:
type: "mysql"
config:
env: "DEV"
username: datahub
password: datahub
database: mediawiki
host_port: localhost:3309
schema_pattern:
ignoreCase: True
@kovid-r
kovid-r / tsbs_questdb_timescaledb_generate_data.sql
Created August 17, 2021 11:06
Sample SQL queries for testing read loads on TimescaleDB and QuestDB via TSBS.
# TimescaleDB Query
SELECT * FROM cpu WHERE usage_user > 90.0
AND time >= '2016-01-01 07:47:52.646325 +0000'
AND time < '2016-01-01 19:47:52.646325 +0000';
# QuestDB Query
SELECT * FROM cpu WHERE usage_user > 90.0
AND timestamp >= '2016-01-01T07:47:52Z'
AND timestamp < '2016-01-01T19:47:52Z';
@kovid-r
kovid-r / dms_access_for_endpoint_trust_relationship_policy.json
Created August 16, 2021 07:41
Add trust relationship policy for dms-access-for-endpoint role.
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "1",
"Effect": "Allow",
"Principal": {
"Service": "dms.amazonaws.com"
},
"Action": "sts:AssumeRole"
@kovid-r
kovid-r / dms_access_for_endpoint_role.sh
Last active August 16, 2021 07:46
Create dms-access-for-endpoint role and attach policy to enable DMS to assume role after connecting to Redshift.
aws iam create-role --role-name dms-access-for-endpoint \
--assume-role-policy-document dms_access_for_endpoint_trust_relationship_policy.json
aws iam attach-role-policy --role-name dms-access-for-endpoint \
--policy-arn arn:aws:iam::aws:policy/service-role/AmazonDMSRedshiftS3Role
@kovid-r
kovid-r / postgresql_redshift_migration_dms_sample_task.sql
Created August 16, 2021 07:00
Sample Load task using DMS for data exported from PostgreSQL to S3, now being imported to Redshift from S3.
COPY /* partner:DMS
{
"Version": "DMS 3.4.4.R1",
"MigrationType": "FullLoad",
"TaskId": "8LRKTDSLNUWKF33K3OUY1NPA23HF3TISTS2BTNX"
}
*/ "public"."test" FROM 's3://dms-bucket/redshift.../.../1/LOAD00000001.csv'
credentials 'aws_iam_role=arn:aws:iam::123412344321:role/dms-access-for-endpoint' delimiter ','
NULL as 'awsdms_null' ESCAPE ACCEPTINVCHARS AS '?' GZIP
@kovid-r
kovid-r / redshift_dms_user_check_privileges.sql
Last active August 16, 2021 06:24
Check if the ARN is able to assume the role.
select has_assumerole_privilege('dwhadmin','arn of the dms-access-for-endpoint role','copy');
select has_assumerole_privilege('dwhadmin','arn of the dms-access-for-endpoint role','unload');
select pg_get_iam_role_by_user('dwhadmin');
select pg_get_grantee_by_iam_role('arn of the dms-access-for-endpoint role');
@kovid-r
kovid-r / tsbs_timescaledb_load.sh
Created August 15, 2021 08:48
TSBS TimescaleDB Load with 8 workers.
[root@ip-benchmark-instance bin]# ./tsbs_load load timescaledb --config=./config.yaml
Using config file: ./config.yaml
time,per. metric/s,metric total,overall metric/s,per. row/s,row total,overall row/s
1629017066,2008653.66,2.008680E+07,2008653.66,178997.65,1.790000E+06,178997.65
1629017076,1290459.37,3.299140E+07,1649558.78,114999.94,2.940000E+06,146999.00
1629017086,1694639.87,4.993780E+07,1664585.74,150999.99,4.450000E+06,148332.66
1629017096,1458699.71,6.452480E+07,1613114.40,129999.97,5.750000E+06,143749.50
1629017106,1627521.36,8.080000E+07,1615995.79,145000.12,7.200000E+06,143999.62
1629017116,1458724.75,9.538780E+07,1589783.18,129995.08,8.500000E+06,141665.46
1629017126,1357760.59,1.089648E+08,1556638.60,121005.40,9.710000E+06,138714.16