Thursday, 2 December 2021

List file names in a directory by file explorer

  • Go to the specific directory where files are stored.
  • Select all files by CNTRL+A.
  • Press Shift and Right click on files
  • Select “copy as path” from right click menu
  • Paste the result in file editor


VLOOKUP - Data Cleansing - MSExcel

 =IF(ISNA(VLOOKUP($B3,G$3:G$26,1,FALSE)), "Not Present", "Present")


=IF(ISNA(VLOOKUP($D3,B$3:B$27,1,FALSE)), "Not Used", "Used")

Sunday, 19 September 2021

Create Sequence in tables

CREATE SEQUENCE public.seq_abcded_id
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

ALTER SEQUENCE public.seq_abcded_id
    OWNER TO postgres;
CREATE TABLE public.abcde
(
    id integer NOT NULL DEFAULT nextval('seq_abcded_id'::regclass),
    name character varying(100),
    PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
);

ALTER TABLE public.abcde
    OWNER to postgres;

Thursday, 26 August 2021

Windows Script to return current path

 cd - To use in the command line



%cd% - To use in the scripts

Java Script - Replace forward slash with backward slash

 str = get_instal_path_org;

res = str.replace(/\//g, "\\");;


Monday, 23 August 2021

AWS - Useful commands

 LINUX Command to connect AWS using KEYS,


export AWS_ACCESS_KEY_ID=AKIAAAAUQG7QC2O4KS6NKGSM; 

export AWS_SECRET_ACCESS_KEY=I1DCBC3nwsRcV8+aoXl0c/PX4iFsTJMr1aZ8EzIDKGSMS;  

aws s3 ls s3://kgsm/active/

s3://kgsm/raw

s3://kgsm/active



WINDOWS Command to connect AWS using KEYS,


setx AWS_ACCESS_KEY_ID AKIAV7UQG7Q7C2O4KS6NKGSM

setx AWS_SECRET_ACCESS_KEY I1DCBC3nwsRcV8+aoXl0c/PX4iFsTJMr1aZ8EzIDKGSMS

setx AWS_DEFAULT_REGION us-east-1

aws s3 ls s3://kgsm/active/


WINDOWS Command to CREATE NEW FOLDER IN AWS and UPLOAD THE FILE


aws s3 cp F:\run.bat s3://kgsm/active/a1/

Tuesday, 17 August 2021

Postgresql : Generate token

 CREATE TABLE adempiere.license_tomato_app

(

    customerid integer,

    name text COLLATE pg_catalog."default",

    location text COLLATE pg_catalog."default",

    key text COLLATE pg_catalog."default" DEFAULT upper(substr(md5((random())::text), 0, 50)),

    plan text COLLATE pg_catalog."default",

    license_start_date timestamp without time zone,

    license_end_date timestamp without time zone,

    last_sync_data_time timestamp without time zone,

    industry_type character varying(50) COLLATE pg_catalog."default",

    business_name character varying(50) COLLATE pg_catalog."default",

    isactive integer DEFAULT 1,

    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,

    updated_at timestamp without time zone,

    created_by integer,

    updated_by integer

)

WITH (

    OIDS = FALSE

)

TABLESPACE pg_default;

Wednesday, 28 July 2021

Getting SELECT to return a constant value even if zero rows match - Postgresql

 SELECT COALESCE(status,'not a valid token') AS status,last_sync_data_time FROM

 (

SELECT 

CASE WHEN CURRENT_TIMESTAMP <= license_end_date THEN 'valid' ELSE 'expired' END AS status, last_sync_data_time

FROM adempiere.license_tomato_app

WHERE key = '7EF3D9B8183D9C7AB90242AF5D011ABE'

union all 

select  null, '1900-01-01 00:00:00' last_sync_data_time where not exists (select 1 from adempiere.license_tomato_app WHERE key = '7EF3D9B8183D9C7AB90242AF5D011ABE')

) t

Wednesday, 7 July 2021

Backup a table in postgresql

 SELECT * INTO abc_bk FROM abc;

Tuesday, 6 July 2021

Open Source Big Data Databases

  • Cassandra
  • CouchDB
  • FlockDB
  • HBase
  • Hibari
  • MongoDB
  • Neo4j
  • OrientDB
  • Riak
  • Terrstore

Thursday, 27 May 2021

Postgresql: Get all tables count from DB

 SELECT schemaname,relname,n_live_tup 

  FROM pg_stat_user_tables 

  ORDER BY n_live_tup DESC;

Postgresql: Get all primary keys from all the tables in DB

SELECT 

kcu.table_schema, kcu.table_name, tco.constraint_name, kcu.ordinal_position AS position, kcu.column_name AS key_column

FROM information_schema.table_constraints tco

JOIN information_schema.key_column_usage kcu 

    ON kcu.constraint_name = tco.constraint_name

    AND kcu.constraint_schema = tco.constraint_schema

    AND kcu.constraint_name = tco.constraint_name

WHERE tco.constraint_type = 'PRIMARY KEY'

ORDER BY 

kcu.table_schema, kcu.table_name, position;


Columns


table_schema - PK schema name

table_name - PK table name

constraint_name - PK constraint name

position - index of column in table (1, 2, ...). 2 or higher means key is composite (contains more than one column)

key_column - PK column name


Wednesday, 19 May 2021

Postgresql: Find the 'N' th occurrences in a string

 Sample Table:

create table filter_table (filter_type varchar(500));

Insert Query:

insert into filter_table (filter_type) values ('1111111 > 22222 > 3333333> 4444444> 123456789 > abcdefghi > 987654321 > zyxwvuts > 0000000 >');


Query to find the occurrences:

select substring(filter_type from '^(([^ ]* ){2})')

from filter_table

DBT - Models

Models are where your developers spend most of their time within a dbt environment. Models are primarily written as a select statement and ...