Arqentum’s gists

Arqentum / postgresql_inplace_dedupe.sql

Created June 19, 2024 01:15

#Postgres inplace dedupe

	# /SD: inplace distinct. MYR-caused duplicate from union query above, not worth the full debug/
	cursor.execute(""" DELETE FROM incentive_activation_rr_invoices a USING (
	SELECT MIN(ctid) as ctid, invoice_id, invoice_li_id, product /SD: PK/
	FROM incentive_activation_rr_invoices
	GROUP BY (invoice_id, invoice_li_id, product) HAVING COUNT(*) > 1
	) b
	WHERE true
	and a.invoice_id = b.invoice_id
	and a.invoice_li_id = b.invoice_li_id
	and a.product = b.product

Arqentum / gsheets_alternate_color_range.txt

Created June 4, 2024 18:48

Google #Sheet alternating range based on cell value

	# Example for 'banding' value in column A2+

	# Format, Conditional formatting:
	Custom Formula
	Apply to range: A2:U50
	Formula: =ISODD(MATCH($A2, UNIQUE($A$2:$A2),0))


	# ArrayFormula:
	={"gg";ARRAYFORMULA(

Arqentum / sublime_fix_alias_git_commit_editor.sh

Last active May 9, 2024 18:23

#Sublime set alias and fix #Git commit editor

	# 1) Run in Terminal:
	# This adds a subl alias to /usr/local/bin/ pointing to Sublime Text 3 app’s binary file.
	# Now running subl in Terminal will launch Sublime Text 3 app.

	sudo ln -s /Applications/Sublime\ Text.app/Contents/SharedSupport/bin/subl /usr/local/bin/subl

	# 2) Run in Terminal:

	git config --global core.editor "subl -n -w"

Arqentum / pandas_to_gcs_gzipped.py

Created April 4, 2024 19:42

#Pandas to #GCS gzipped

	csv_file_name = get_file_name(execution_date=context['execution_date'], ripley_table_name=self.ripley_table_name)
	LOGGER.info('Uploading %s to %s' % (csv_file_name, self.gcs_bucket_name))

	# Ensure that we properly encode and escape the JSON string
	pandas_df['payload'] = pandas_df['payload'].apply(json.dumps)
	pandas_df['schema'] = pandas_df['schema'].apply(json.dumps)
	correct_csv = pandas_df.to_csv(encoding='utf-8', header=False, index=False, doublequote=True, quoting=csv.QUOTE_ALL)

	blob = bucket.blob(csv_file_name)
	blob.content_encoding = "gzip"

Arqentum / BigQuery join to latest

Created March 5, 2024 14:42

#BigQuery join to latest by order only 1

	-- https://stackoverflow.com/questions/73825482/bigquery-where-clause-using-column-from-outside-the-subquery?rq=1


	select aggregate_id, JSON_VALUE(re.payload, '$.currency'), JSON_VALUE(re.payload, '$.legalEntityId')
	, re.created_on
	, fx., re.
	from `finance-datamart-stage-001`.sandbox.ripley_raw_events_ext re
	left join `finance-datamart-prod-001`.testing_process.fx_rates fx
	on fx.source_currency = JSON_VALUE(re.payload, '$.currency') and fx.target_currency in ('USD', 'EUR')
	and (

Arqentum / airflow_debugging_dag.py

Created January 31, 2024 22:09

#Airflow allow debugging dag #Python

	# add at the end of dag file

	if __name__ == "__main__":
	from airflow.utils.state import State

	dag.clear()
	dag.run()

Arqentum / epic7.bat

Created January 24, 2024 16:22

#Adb headless batch

adb connect 127.0.0.1:5555 && cd /d s:\git\headless_adb && .\venv\Scripts\python.exe epic7.py

Arqentum / bigquery_table_ddl_as_json.sh

Created November 9, 2023 00:24

#BigQuery get table DDL as json schema

	# Activate console to run "bq" commands:
	# https://console.cloud.google.com/bigquery?cloudshell=true

	# bq show --format=prettyjson bigquery-public-data:samples.wikipedia \| jq '.schema.fields'

	bq show --format=prettyjson <project>:<bucket>.<table> \| jq '.schema.fields'

Arqentum / pyenv_create_alias_pyver.sh

Last active November 3, 2023 19:05

#Python #PYENV create alias activate version

	PYENV_PYTHON_VERSION=3.8.18
	VENV_NAME=revrec-airflow-3.8
	pyenv install $PYENV_PYTHON_VERSION
	pyenv virtualenv $PYENV_PYTHON_VERSION $VENV_NAME
	pyenv activate $VENV_NAME

	#Switch between Python versions
	#To select a Pyenv-installed Python as the version to use, run one of the following commands:

	pyenv shell <version> -- select just for current shell session

Arqentum / bq_in_datagrip.txt

Created October 10, 2023 17:52

#Datagrip auth to #BigQuery

	Application Default Credential option in login options on connection setup step

	make sure to
	> gcloud auth application-default login
	before