Kovid Rathee kovid-r

## creating_a_date_dimension_table_in_postgresql.sql
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,

## get-medium-stats.js
const totalTypes = {
  VIEWS: 2,
  READS: 3,
  FANS: 5
};

const getTotal = tableColumn =>
  [
    ...document.querySelectorAll(
      `td:nth-child(${tableColumn}) > span.sortableTable-number`

## mysql-datahub-user.sql
CREATE USER 'datahub'@'%' IDENTIFIED BY PASSWORD 'datahub';
GRANT USAGE ON *.* TO 'datahub'@'%';
GRANT SELECT ON 'mediawiki'.* TO 'datahub'@'%';

## mysql-datahub.yml
source:
  type: "mysql"
  config:
    env: "DEV"
    username: datahub
    password: datahub
    database: mediawiki
    host_port: localhost:3309
    schema_pattern:
      ignoreCase: True

## tsbs_questdb_timescaledb_generate_data.sql
# 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';

## dms_access_for_endpoint_trust_relationship_policy.json
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "1",
      "Effect": "Allow",
      "Principal": {
        "Service": "dms.amazonaws.com"
      },
      "Action": "sts:AssumeRole"

## dms_access_for_endpoint_role.sh
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

## postgresql_redshift_migration_dms_sample_task.sql
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

## redshift_dms_user_check_privileges.sql
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');

## tsbs_timescaledb_load.sh
[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
	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,
	const totalTypes = {
	VIEWS: 2,
	READS: 3,
	FANS: 5
	};

	const getTotal = tableColumn =>
	[
	...document.querySelectorAll(
	`td:nth-child(${tableColumn}) > span.sortableTable-number`
	CREATE USER 'datahub'@'%' IDENTIFIED BY PASSWORD 'datahub';
	GRANT USAGE ON . TO 'datahub'@'%';
	GRANT SELECT ON 'mediawiki'.* TO 'datahub'@'%';
	source:
	type: "mysql"
	config:
	env: "DEV"
	username: datahub
	password: datahub
	database: mediawiki
	host_port: localhost:3309
	schema_pattern:
	ignoreCase: True
	# 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';
	{
	"Version": "2012-10-17",
	"Statement": [
	{
	"Sid": "1",
	"Effect": "Allow",
	"Principal": {
	"Service": "dms.amazonaws.com"
	},
	"Action": "sts:AssumeRole"
	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
	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
	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');
	[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