Instantly share code, notes, and snippets.
🏠
Working from home
Kovid Rathee kovid-r
🏠
Working from home
kovid-r /creating_a_date_dimension_table_in_postgresql.sql
CreatedOctober 18, 2022 10:25 — forked frommtucker/creating_a_date_dimension_table_in_postgresql.sql
Creating a date dimension table in PostgreSQL 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
| DROPTABLE if exists d_date; | |
| CREATETABLEd_date | |
| ( | |
| date_dim_idINTNOT NULL, | |
| date_actualDATENOT NULL, | |
| epochBIGINTNOT NULL, | |
| day_suffixVARCHAR(4)NOT NULL, | |
| day_nameVARCHAR(9)NOT NULL, | |
| day_of_weekINTNOT NULL, |
kovid-r /get-medium-stats.js
CreatedJanuary 9, 2022 13:36 — forked fromigeligel/get-medium-stats.js
medium-get-totals 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
| consttotalTypes={ | |
| VIEWS:2, | |
| READS:3, | |
| FANS:5 | |
| }; | |
| constgetTotal=tableColumn=> | |
| [ | |
| ...document.querySelectorAll( | |
| `td:nth-child(${tableColumn}) > span.sortableTable-number` |
kovid-r /mysql-datahub-user.sql
CreatedOctober 18, 2021 11:03
User permissions for DataHub to connect to MySQL and fetch metadata. 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
| CREATEUSER 'datahub'@'%' IDENTIFIED BY PASSWORD'datahub'; | |
| GRANT USAGEON*.* TO'datahub'@'%'; | |
| GRANTSELECTON'mediawiki'.* TO'datahub'@'%'; |
kovid-r /mysql-datahub.yml
CreatedOctober 18, 2021 10:59
Metadata Source configuration YAML file for adding MySQL to DataHub 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
CreatedAugust 17, 2021 11:06
Sample SQL queries for testing read loads on TimescaleDB and QuestDB via TSBS. 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 cpuWHERE usage_user>90.0 | |
| ANDtime>='2016-01-01 07:47:52.646325 +0000' | |
| ANDtime<'2016-01-01 19:47:52.646325 +0000'; | |
| # QuestDB Query | |
| SELECT*FROM cpuWHERE usage_user>90.0 | |
| ANDtimestamp>='2016-01-01T07:47:52Z' | |
| ANDtimestamp<'2016-01-01T19:47:52Z'; |
kovid-r /dms_access_for_endpoint_trust_relationship_policy.json
CreatedAugust 16, 2021 07:41
Add trust relationship policy for dms-access-for-endpoint role. 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 activeAugust 16, 2021 07:46
Create dms-access-for-endpoint role and attach policy to enable DMS to assume role after connecting to Redshift. 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
CreatedAugust 16, 2021 07:00
Sample Load task using DMS for data exported from PostgreSQL to S3, now being imported to Redshift from S3. 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',' | |
| NULLas'awsdms_null' ESCAPE ACCEPTINVCHARSAS'?' GZIP |
kovid-r /redshift_dms_user_check_privileges.sql
Last activeAugust 16, 2021 06:24
Check if the ARN is able to assume the role. 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
CreatedAugust 15, 2021 08:48
TSBS TimescaleDB Load with 8 workers. 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