# New Oracle load manual
## Steps
- Full load (sdk)
- jdbc
- run dmls (sdk)
## Full load
In goldengate_sdk venv, run:

db_name=planck
tables=PLANCK.TMS_CONTAS_PAGAR

invoke oracle.ingestion.load-tables-full -e prd -s ${db_name} -t ${tables} -w 1

## JDBC Settings

GCP_PROJECT=b2w-bee-analytics
TEMPLATE_BUCKET_NAME=${GCP_PROJECT}-dataflow-templates
RELEASE_FOLDER=stable
RELEASE_VERSION="v2.5.2"
TEMPLATE_NAME=JdbcParsedToBigQuery
TEMPLATE_GCS_PATH=gs://${TEMPLATE_BUCKET_NAME}/beejar/${RELEASE_FOLDER}/${RELEASE_VERSION}/${TEMPLATE_NAME}

INSTANCE_NAME=bwpkpr
DATABASE_NAME=planck
OWNER_NAME=planck
TABLE_NAME=tms_contas_pagar # all low case
HOST=dbpkpr-scan.back.b2w
PORT=1521
SERVICE_NAME=SRV_OGG_PLK
CONNECTION_USERNAME=ggadmin
CONNECTION_PASSWORD=dbgg19pwd
JDBC_CONNECTION_URL=jdbc:oracle:thin:@//$HOST:$PORT/$SERVICE_NAME  # jdbc:oracle:thin:@$HOST:$PORT:$SERVICE_NAME
JDBC_CONNECTION_PROPERTIES="defaultRowPrefetch=100000"
DRIVER_PATHS=gs://b2w-bee-analytics-jdbc-drivers/oracle/ojdbc6.jar
DRIVER_CLASS_NAME=oracle.jdbc.driver.OracleDriver

JOB_NAME=oracle-${DATABASE_NAME}-${OWNER_NAME//_/-}-${TABLE_NAME//_/-}-to-bq
PROJECT_ID=b2w-bee-analytics
REGION_NAME=us-east1
TABLE_COLUMNS="*"
SOURCE_SQL_QUERY_FILTER="WHERE DT_REGISTRO >= TO_DATE('2022-03-07', 'yyyy-mm-dd') AND DT_REGISTRO <= TO_DATE('2022-03-08', 'yyyy-mm-dd')"
SOURCE_SQL_QUERY="SELECT upper('${OWNER_NAME}.${TABLE_NAME}') as \"table\", current_timestamp as \"op_ts\", timestamp_to_scn(sysdate) as \"csn\", a.* FROM (SELECT ${TABLE_COLUMNS} from ${OWNER_NAME}.${TABLE_NAME} ${SOURCE_SQL_QUERY_FILTER}) a"
TABLE_ID=$GCP_PROJECT:raw_${DATABASE_NAME}_ingestion.${INSTANCE_NAME}_${OWNER_NAME}_${TABLE_NAME}
PATH_TO_TEMP_DIR_ON_GCS=gs://b2w-bee-analytics-bee-transient/$JOB_NAME
MAX_WORKERS=4
WORKER_TYPE=n1-standard-2
NETWORK=shared-bee-interconnect
SUBNETWORK=https://www.googleapis.com/compute/v1/projects/b2w-bee-interconnect/regions/us-east1/subnetworks/b2w-bee-analytics-stg-subnet-us-east1-1

gcloud dataflow flex-template run $JOB_NAME \
    --template-file-gcs-location $TEMPLATE_GCS_PATH \
    --project $GCP_PROJECT \
    --region us-east1 \
    --parameters parserName="OracleJdbc" \
    --parameters autoscalingAlgorithm="THROUGHPUT_BASED" \
    --parameters driverJars=$DRIVER_PATHS \
    --parameters driverClassName=$DRIVER_CLASS_NAME \
    --parameters connectionURL=$JDBC_CONNECTION_URL \
    --parameters connectionProperties=$JDBC_CONNECTION_PROPERTIES \
    --parameters username=$CONNECTION_USERNAME \
    --parameters password=$CONNECTION_PASSWORD \
    --parameters ^:^query="$SOURCE_SQL_QUERY" \
    --parameters outputTable=$TABLE_ID \
    --parameters bigQueryLoadingTemporaryDirectory=$PATH_TO_TEMP_DIR_ON_GCS \
    --parameters defaultWorkerLogLevel=INFO \
    --additional-user-labels gcp_component="dataflow_job" \
    --additional-user-labels bee_squad="data_core" \
    --additional-user-labels entity="ingestion" \
    --additional-user-labels source_domain="oracle_database" \
    --additional-user-labels bee_component="source_integration_pipeline" \
    --additional-user-labels pipeline_component="jdbc_to_bq_ingestion" \
    --additional-user-labels environment="prd" \
    --additional-user-labels resource_name=$JOB_NAME \
    --worker-machine-type $WORKER_TYPE \
    --max-workers $MAX_WORKERS \
    --subnetwork $SUBNETWORK

## Run dmls
In goldengate_sdk venv, run:

db_name=planck
tables=PLANCK.TMS_CONTAS_PAGAR

invoke oracle.utils.run-db-tables-dmls -e prd -s $db_name -w 2 -t $tables