antoniocachuan

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

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 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

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

--bigquery

antoniocachuan

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

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

#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

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

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

python3 \
    pipeline_dataflow.py \
    --project \
    PROJECT_ID \
    --runner DirectRunner \
    --temp_location \
    gs://PROJECT_ID/dataflow/tmp \
    --job_name dataflow-covid \
    --region us-central1