kovid-r
/ creating_a_date_dimension_table_in_postgresql.sql
Created
October 18, 2022 10:25
— forked from mtucker/creating_a_date_dimension_table_in_postgresql.sql
Creating a date dimension table in PostgreSQL
View creating_a_date_dimension_table_in_postgresql.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
/ get-medium-stats.js
Created
January 9, 2022 13:36
— forked from igeligel/get-medium-stats.js
medium-get-totals
View get-medium-stats.js
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| const totalTypes = { | |
| VIEWS: 2, | |
| READS: 3, | |
| FANS: 5 | |
| }; | |
| const getTotal = tableColumn => | |
| [ | |
| ...document.querySelectorAll( | |
| `td:nth-child(${tableColumn}) > span.sortableTable-number` |
kovid-r
/ mysql-datahub-user.sql
Created
October 18, 2021 11:03
User permissions for DataHub to connect to MySQL and fetch metadata.
View mysql-datahub-user.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE USER 'datahub'@'%' IDENTIFIED BY PASSWORD 'datahub'; | |
| GRANT USAGE ON *.* TO 'datahub'@'%'; | |
| GRANT SELECT ON 'mediawiki'.* TO 'datahub'@'%'; |
kovid-r
/ mysql-datahub.yml
Created
October 18, 2021 10:59
Metadata Source configuration YAML file for adding MySQL to DataHub
View mysql-datahub.yml
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| source: | |
| type: "mysql" | |
| config: | |
| env: "DEV" | |
| username: datahub | |
| password: datahub | |
| database: mediawiki | |
| host_port: localhost:3309 | |
| schema_pattern: | |
| ignoreCase: True |
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.
View tsbs_questdb_timescaledb_generate_data.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # 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
/ dms_access_for_endpoint_trust_relationship_policy.json
Created
August 16, 2021 07:41
Add trust relationship policy for dms-access-for-endpoint role.
View dms_access_for_endpoint_trust_relationship_policy.json
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "Version": "2012-10-17", | |
| "Statement": [ | |
| { | |
| "Sid": "1", | |
| "Effect": "Allow", | |
| "Principal": { | |
| "Service": "dms.amazonaws.com" | |
| }, | |
| "Action": "sts:AssumeRole" |
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.
View dms_access_for_endpoint_role.sh
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
/ 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.
View postgresql_redshift_migration_dms_sample_task.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
/ redshift_dms_user_check_privileges.sql
Last active
August 16, 2021 06:24
Check if the ARN is able to assume the role.
View redshift_dms_user_check_privileges.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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
/ tsbs_timescaledb_load.sh
Created
August 15, 2021 08:48
TSBS TimescaleDB Load with 8 workers.
View tsbs_timescaledb_load.sh
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [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 |
NewerOlder