Antonio Cachuan antoniocachuan

GCP Professional Data Engineer. Love Data, keep coding 👍

antoniocachuan / create_table_bq.sql

Last active June 18, 2022 03:55

Create table BigQuery

	CREATE TABLE contact_dataset.t_contact (

	id STRING,
	email STRING,
	phone STRING,
	year STRING
	)
	OPTIONS (
	expiration_timestamp = TIMESTAMP '2024-01-01 00:00:00 UTC',
	description = 'exmple table for column level access',

antoniocachuan / example-bigquery-storage-read-api.py

Last active June 22, 2022 04:27

Example BigQuery Storage Read API

	from google.cloud.bigquery_storage import BigQueryReadClient
	from google.cloud.bigquery_storage import types
	from google.cloud import bigquery_storage
	import pandas
	import os

	os.environ["GOOGLE_APPLICATION_CREDENTIALS"] ='key.json'

	project_id_billing = 'YOUR-PROJECT'# A Project where you have biquery.readsession permission

antoniocachuan / merge_example.sql

Created November 26, 2021 03:04

Merge

	MERGE INTO reporting.transaction_model A
	USING (
	SELECT transaction_id, transaction_date, price, store_name, last_updated
	FROM
	EXTERNAL_QUERY
	(
	"projects/datapath/locations/us/connections/databaseconnection",
	"SELECT CAST(transaction_id AS VARCHAR(100)), store_name, transaction_date, price, last_updated FROM public.transaction WHERE transaction_date BETWEEN NOW() - INTERVAL '2 DAY' AND NOW();"
	)
	) B

antoniocachuan / create.sql

Created November 26, 2021 02:41

Create Scripts

	--postgresql
	CREATE TABLE transaction (
	transaction_id serial PRIMARY KEY,
	store_name VARCHAR ( 100 ),
	transaction_date TIMESTAMP,
	price VARCHAR ( 50 ),
	last_updated TIMESTAMP
	);

	--bigquery

antoniocachuan / gist:a5b98216264ea95b3a723ebb9bd56ae0

Created October 26, 2021 06:43

sesion_01_bigquery

	CREATE OR REPLACE TABLE area_comercial_ventas.tipo_documento_persona
	/CREATE TABLE IF NOT EXISTS/
	(
	codigo_tipo_documento int64 NOT NULL options(description="Código del tipo de documento de la persona"),
	tipo_documento string NOT NULL options(description="Tipo de documento de identidad de la persona"),
	sistema_origen string NOT NULL options(description="Descripción del sistema origen"),
	fecha_creacion timestamp NOT NULL options(description="Fecha y hora de creación del registro"),
	usuario_creacion string NOT NULL options(description="Usuario que crea el registro")
	)
	options

antoniocachuan / simplecreate.sql

Created September 7, 2021 03:32

Simple Create for PostgreSQL

	CREATE TABLE transactions (
	transaction_id serial PRIMARY KEY,
	product VARCHAR ( 50 ),
	category VARCHAR ( 50 ),
	email VARCHAR ( 255 ),
	created_on TIMESTAMP
	);

	insert into transactions values (1, 'soda', 'drinks', '', '2021-08-08T01:10:45')

antoniocachuan / keyless-api.py

Last active August 26, 2021 04:32

Using Keyless API Google Cloud

	#Read the full article on https://medium.com/@alipazaga07

	import os
	from google.cloud import bigquery
	# Reference the SA
	os.environ["GOOGLE_CLOUD_PROJECT"] ='datapath'. #The GCP project ID
	os.environ["GOOGLE_APPLICATION_CREDENTIALS"] ='configoutput.json' #configuration file
	# BigQuery client object.
	client = bigquery.Client()
	query = """

antoniocachuan / create_dataflow_template.sh

Created April 30, 2021 03:07

Create a Dataflow Template

	python3 \
	pipeline_dataflow.py \
	--project \
	PROJECT-ID \
	--runner DataflowRunner \
	--temp_location \
	gs://BUCKET-NAME/dataflow/tmp \
	--template_location \
	gs://BUCKET-NAME/dataflow/template/templatefile \
	--job_name dataflow-covid \

antoniocachuan / pipeline.YAML

Created April 30, 2021 02:48

Launch Dataflow and write in Pub/Sub

	main:
	steps:
	- init:
	assign:
	- project: ${sys.get_env("GOOGLE_CLOUD_PROJECT_ID")}
	- region: "us-central1"
	- zone: "us-central1-a"
	- topic: "TOPIC_NAME"
	- job_name: "covid_job"
	- input_script: "gs://BUCKET_NAME/dataflow/template/templatefile"

antoniocachuan / apache_beam_execution.sh

Created April 27, 2021 03:06

Execute locally Apache Beam pipeline