colab-enterprise/Synthetic-Data-Generation-Orders.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "clfviNVNXpqj"
   },
   "source": [
    "### <font color='#4285f4'>Overview</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Quickly generate data using BigQuery.  This will generate random order data for the customers based upon the menu items in the system.\n",
    "\n",
    "\n",
    "Process Flow:\n",
    "\n",
    "1. Create the store table and populate it\n",
    "2. Create the order and order detail table\n",
    "3. Get the maximum value (primary key) of the store, customer and menu tables.\n",
    "    * a. We are assuming the ids are consecutive, starting at one and have no gaps.\n",
    "4. Create 1 million orders by using the UNNEST(GENERATE_ARRAY(1, 1000000)) AS element in a BigQuery insert statement. Assigned random foreign key values for the store, customer and menu items.\n",
    "5. Create 1 to 5 order detail records for each order record. Weight orders with less items as a higher probability.\n",
    "6. Verify that our order detail table has details for each order.\n",
    "\n",
    "You can run this over and over to generate more records.\n",
    "\n",
    "Cost:\n",
    "* Low: BigQuery\n",
    "* Medium: Remember to stop your Colab Enterprise Notebook Runtime\n",
    "\n",
    "Author: \n",
    "* Adam Paternostro"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "W3Q8_NLjj3Xw"
   },
   "source": [
    "### <font color='#4285f4'>License</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "qJxJqWa1j3Xw"
   },
   "outputs": [],
   "source": [
    "##################################################################################\n",
    "# Copyright 2024 Google LLC\n",
    "#\n",
    "# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
    "# you may not use this file except in compliance with the License.\n",
    "# You may obtain a copy of the License at\n",
    "#\n",
    "#     https://www.apache.org/licenses/LICENSE-2.0\n",
    "#\n",
    "# Unless required by applicable law or agreed to in writing, software\n",
    "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
    "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
    "# See the License for the specific language governing permissions and\n",
    "# limitations under the License.\n",
    "###################################################################################"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uSra7USNj3Xx"
   },
   "source": [
    "### <font color='#4285f4'>Pip Installs</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "IBtPiyK3kKwS"
   },
   "outputs": [],
   "source": [
    "# PIP Installs\n",
    "import sys\n",
    "\n",
    "# https://PLACEHOLDER.com/index.html\n",
    "#!{sys.executable} -m pip install PLACEHOLDER"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "DszuLZoo9A7k"
   },
   "source": [
    "### <font color='#4285f4'>Initialize</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "bhKxJadjWa1R"
   },
   "outputs": [],
   "source": [
    "from PIL import Image\n",
    "import IPython.display\n",
    "import google.auth\n",
    "import requests\n",
    "import json\n",
    "import uuid\n",
    "import base64\n",
    "import os\n",
    "import cv2\n",
    "import random\n",
    "import time\n",
    "import datetime\n",
    "import base64\n",
    "import random\n",
    "\n",
    "import logging\n",
    "from tenacity import retry, wait_exponential, stop_after_attempt, before_sleep_log, retry_if_exception"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "MSC6-rboip3h"
   },
   "outputs": [],
   "source": [
    "# Set these (run this cell to verify the output)\n",
    "\n",
    "bigquery_location = \"${bigquery_location}\"\n",
    "region = \"${region}\"\n",
    "location = \"${location}\"\n",
    "storage_account = \"${chocolate_ai_bucket}\"\n",
    "public_storage_storage_account = \"data-analytics-golden-demo\"\n",
    "table_name = \"order\"\n",
    "dataset_name = \"${bigquery_chocolate_ai_dataset}\"\n",
    "\n",
    "# Get the current date and time\n",
    "now = datetime.datetime.now()\n",
    "\n",
    "# Format the date and time as desired\n",
    "formatted_date = now.strftime(\"%Y-%m-%d-%H-%M\")\n",
    "\n",
    "# Get some values using gcloud\n",
    "project_id = !(gcloud config get-value project)\n",
    "user = !(gcloud auth list --filter=status:ACTIVE --format=\"value(account)\")\n",
    "\n",
    "if len(project_id) != 1:\n",
    "  raise RuntimeError(f\"project_id is not set: {project_id}\")\n",
    "project_id = project_id[0]\n",
    "\n",
    "if len(user) != 1:\n",
    "  raise RuntimeError(f\"user is not set: {user}\")\n",
    "user = user[0]\n",
    "\n",
    "print(f\"project_id = {project_id}\")\n",
    "print(f\"user = {user}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0mSjSjjylFnR"
   },
   "source": [
    "### <font color='#4285f4'>Helper Methods</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "be6n7F9IkdbT"
   },
   "source": [
    "#### restAPIHelper\n",
    "Calls the Google Cloud REST API using the current users credentials."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "w_QjQhvWkesy"
   },
   "outputs": [],
   "source": [
    "def restAPIHelper(url: str, http_verb: str, request_body: str) -> str:\n",
    "  \"\"\"Calls the Google Cloud REST API passing in the current users credentials\"\"\"\n",
    "\n",
    "  import requests\n",
    "  import google.auth\n",
    "  import json\n",
    "\n",
    "  # Get an access token based upon the current user\n",
    "  creds, project = google.auth.default()\n",
    "  auth_req = google.auth.transport.requests.Request()\n",
    "  creds.refresh(auth_req)\n",
    "  access_token=creds.token\n",
    "\n",
    "  headers = {\n",
    "    \"Content-Type\" : \"application/json\",\n",
    "    \"Authorization\" : \"Bearer \" + access_token\n",
    "  }\n",
    "\n",
    "  if http_verb == \"GET\":\n",
    "    response = requests.get(url, headers=headers)\n",
    "  elif http_verb == \"POST\":\n",
    "    response = requests.post(url, json=request_body, headers=headers)\n",
    "  elif http_verb == \"PUT\":\n",
    "    response = requests.put(url, json=request_body, headers=headers)\n",
    "  elif http_verb == \"PATCH\":\n",
    "    response = requests.patch(url, json=request_body, headers=headers)\n",
    "  elif http_verb == \"DELETE\":\n",
    "    response = requests.delete(url, headers=headers)\n",
    "  else:\n",
    "    raise RuntimeError(f\"Unknown HTTP verb: {http_verb}\")\n",
    "\n",
    "  if response.status_code == 200:\n",
    "    return json.loads(response.content)\n",
    "    #image_data = json.loads(response.content)[\"predictions\"][0][\"bytesBase64Encoded\"]\n",
    "  else:\n",
    "    error = f\"Error restAPIHelper -> ' Status: '{response.status_code}' Text: '{response.text}'\"\n",
    "    raise RuntimeError(error)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### RetryCondition (for retrying LLM calls)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def RetryCondition(error):\n",
    "  error_string = str(error)\n",
    "  print(error_string)\n",
    "\n",
    "  retry_errors = [\n",
    "      \"RESOURCE_EXHAUSTED\",\n",
    "      \"No content in candidate\",\n",
    "      # Add more error messages here as needed\n",
    "  ]\n",
    "\n",
    "  for retry_error in retry_errors:\n",
    "    if retry_error in error_string:\n",
    "      print(\"Retrying...\")\n",
    "      return True\n",
    "\n",
    "  return False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "UCSP64xskgje"
   },
   "source": [
    "#### Gemini LLM (Pro 1.0 , Pro 1.5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "6M2ojhjYkhjy"
   },
   "outputs": [],
   "source": [
    "@retry(wait=wait_exponential(multiplier=1, min=1, max=60), stop=stop_after_attempt(10), retry=retry_if_exception(RetryCondition), before_sleep=before_sleep_log(logging.getLogger(), logging.INFO))\n",
    "def GeminiLLM(prompt, model = \"gemini-2.0-flash\", response_schema = None,\n",
    "                 temperature = 1, topP = 1, topK = 32):\n",
    "\n",
    "  # https://cloud.google.com/vertex-ai/generative-ai/docs/model-reference/inference#supported_models\n",
    "  # model = \"gemini-2.0-flash\"\n",
    "\n",
    "  llm_response = None\n",
    "  if temperature < 0:\n",
    "    temperature = 0\n",
    "\n",
    "  creds, project = google.auth.default()\n",
    "  auth_req = google.auth.transport.requests.Request() # required to acess access token\n",
    "  creds.refresh(auth_req)\n",
    "  access_token=creds.token\n",
    "\n",
    "  headers = {\n",
    "      \"Content-Type\" : \"application/json\",\n",
    "      \"Authorization\" : \"Bearer \" + access_token\n",
    "  }\n",
    "\n",
    "  # https://cloud.google.com/vertex-ai/generative-ai/docs/model-reference/inference\n",
    "  url = f\"https://{location}-aiplatform.googleapis.com/v1/projects/{project_id}/locations/{location}/publishers/google/models/{model}:generateContent\"\n",
    "\n",
    "  generation_config = {\n",
    "    \"temperature\": temperature,\n",
    "    \"topP\": topP,\n",
    "    \"maxOutputTokens\": 8192,\n",
    "    \"candidateCount\": 1,\n",
    "    \"responseMimeType\": \"application/json\",\n",
    "  }\n",
    "\n",
    "  # Add inthe response schema for when it is provided\n",
    "  if response_schema is not None:\n",
    "    generation_config[\"responseSchema\"] = response_schema\n",
    "\n",
    "  if model == \"gemini-2.0-flash\":\n",
    "    generation_config[\"topK\"] = topK\n",
    "\n",
    "  payload = {\n",
    "    \"contents\": {\n",
    "      \"role\": \"user\",\n",
    "      \"parts\": {\n",
    "          \"text\": prompt\n",
    "      },\n",
    "    },\n",
    "    \"generation_config\": {\n",
    "      **generation_config\n",
    "    },\n",
    "    \"safety_settings\": {\n",
    "      \"category\": \"HARM_CATEGORY_SEXUALLY_EXPLICIT\",\n",
    "      \"threshold\": \"BLOCK_LOW_AND_ABOVE\"\n",
    "    }\n",
    "  }\n",
    "\n",
    "  response = requests.post(url, json=payload, headers=headers)\n",
    "\n",
    "  if response.status_code == 200:\n",
    "    try:\n",
    "      json_response = json.loads(response.content)\n",
    "    except Exception as error:\n",
    "      raise RuntimeError(f\"An error occurred parsing the JSON: {error}\")\n",
    "\n",
    "    if \"candidates\" in json_response:\n",
    "      candidates = json_response[\"candidates\"]\n",
    "      if len(candidates) > 0:\n",
    "        candidate = candidates[0]\n",
    "        if \"content\" in candidate:\n",
    "          content = candidate[\"content\"]\n",
    "          if \"parts\" in content:\n",
    "            parts = content[\"parts\"]\n",
    "            if len(parts):\n",
    "              part = parts[0]\n",
    "              if \"text\" in part:\n",
    "                text = part[\"text\"]\n",
    "                llm_response = text\n",
    "              else:\n",
    "                raise RuntimeError(\"No text in part: {response.content}\")\n",
    "            else:\n",
    "              raise RuntimeError(\"No parts in content: {response.content}\")\n",
    "          else:\n",
    "            raise RuntimeError(\"No parts in content: {response.content}\")\n",
    "        else:\n",
    "          raise RuntimeError(\"No content in candidate: {response.content}\")\n",
    "      else:\n",
    "        raise RuntimeError(\"No candidates: {response.content}\")\n",
    "    else:\n",
    "      raise RuntimeError(\"No candidates: {response.content}\")\n",
    "\n",
    "    # Remove some typically response characters (if asking for a JSON reply)\n",
    "    llm_response = llm_response.replace(\"```json\",\"\")\n",
    "    llm_response = llm_response.replace(\"```\",\"\")\n",
    "    llm_response = llm_response.replace(\"\\n\",\"\")\n",
    "\n",
    "    return llm_response\n",
    "\n",
    "  else:\n",
    "    raise RuntimeError(f\"Error with prompt:'{prompt}'  Status:'{response.status_code}' Text:'{response.text}'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vbNakk0fkjTj"
   },
   "source": [
    "#### Gemini LLM - Multimodal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "3H0r26TxklPu"
   },
   "outputs": [],
   "source": [
    "@retry(wait=wait_exponential(multiplier=1, min=1, max=60), stop=stop_after_attempt(10), retry=retry_if_exception(RetryCondition), before_sleep=before_sleep_log(logging.getLogger(), logging.INFO))\n",
    "def GeminiLLM_Multimodal(multimodal_prompt_list, model = \"gemini-2.0-flash\", response_schema = None,\n",
    "                 temperature = 1, topP = 1, topK = 32):\n",
    "\n",
    "  # https://cloud.google.com/vertex-ai/generative-ai/docs/model-reference/inference#supported_models\n",
    "  # model = \"gemini-2.0-flash\"\n",
    "\n",
    "  llm_response = None\n",
    "  if temperature < 0:\n",
    "    temperature = 0\n",
    "\n",
    "  creds, project = google.auth.default()\n",
    "  auth_req = google.auth.transport.requests.Request() # required to acess access token\n",
    "  creds.refresh(auth_req)\n",
    "  access_token=creds.token\n",
    "\n",
    "  headers = {\n",
    "      \"Content-Type\" : \"application/json\",\n",
    "      \"Authorization\" : \"Bearer \" + access_token\n",
    "  }\n",
    "\n",
    "  # https://cloud.google.com/vertex-ai/generative-ai/docs/model-reference/inference\n",
    "  url = f\"https://{location}-aiplatform.googleapis.com/v1/projects/{project_id}/locations/{location}/publishers/google/models/{model}:generateContent\"\n",
    "\n",
    "  generation_config = {\n",
    "    \"temperature\": temperature,\n",
    "    \"topP\": topP,\n",
    "    \"maxOutputTokens\": 8192,\n",
    "    \"candidateCount\": 1,\n",
    "    \"responseMimeType\": \"application/json\",\n",
    "  }\n",
    "\n",
    "  # Add inthe response schema for when it is provided\n",
    "  if response_schema is not None:\n",
    "    generation_config[\"responseSchema\"] = response_schema\n",
    "\n",
    "  if model == \"gemini-2.0-flash\":\n",
    "    generation_config[\"topK\"] = topK\n",
    "\n",
    "  payload = {\n",
    "    \"contents\": {\n",
    "      \"role\": \"user\",\n",
    "      \"parts\": multimodal_prompt_list\n",
    "    },\n",
    "    \"generation_config\": {\n",
    "      **generation_config\n",
    "    },\n",
    "    \"safety_settings\": {\n",
    "      \"category\": \"HARM_CATEGORY_SEXUALLY_EXPLICIT\",\n",
    "      \"threshold\": \"BLOCK_LOW_AND_ABOVE\"\n",
    "    }\n",
    "  }\n",
    "\n",
    "  response = requests.post(url, json=payload, headers=headers)\n",
    "\n",
    "  if response.status_code == 200:\n",
    "    try:\n",
    "      json_response = json.loads(response.content)\n",
    "    except Exception as error:\n",
    "      raise RuntimeError(f\"An error occurred parsing the JSON: {error}\")\n",
    "\n",
    "    if \"candidates\" in json_response:\n",
    "      candidates = json_response[\"candidates\"]\n",
    "      if len(candidates) > 0:\n",
    "        candidate = candidates[0]\n",
    "        if \"content\" in candidate:\n",
    "          content = candidate[\"content\"]\n",
    "          if \"parts\" in content:\n",
    "            parts = content[\"parts\"]\n",
    "            if len(parts):\n",
    "              part = parts[0]\n",
    "              if \"text\" in part:\n",
    "                text = part[\"text\"]\n",
    "                llm_response = text\n",
    "              else:\n",
    "                raise RuntimeError(\"No text in part: {response.content}\")\n",
    "            else:\n",
    "              raise RuntimeError(\"No parts in content: {response.content}\")\n",
    "          else:\n",
    "            raise RuntimeError(\"No parts in content: {response.content}\")\n",
    "        else:\n",
    "          raise RuntimeError(\"No content in candidate: {response.content}\")\n",
    "      else:\n",
    "        raise RuntimeError(\"No candidates: {response.content}\")\n",
    "    else:\n",
    "      raise RuntimeError(\"No candidates: {response.content}\")\n",
    "\n",
    "    # Remove some typically response characters (if asking for a JSON reply)\n",
    "    llm_response = llm_response.replace(\"```json\",\"\")\n",
    "    llm_response = llm_response.replace(\"```\",\"\")\n",
    "    llm_response = llm_response.replace(\"\\n\",\"\")\n",
    "\n",
    "    return llm_response\n",
    "\n",
    "  else:\n",
    "    raise RuntimeError(f\"Error with prompt:'{prompt}'  Status:'{response.status_code}' Text:'{response.text}'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "RtE_Q-H6lJQT"
   },
   "source": [
    "#### Imagen3 Image Generation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "hnfwd_iplO9c"
   },
   "outputs": [],
   "source": [
    "def ImageGen(prompt):\n",
    "  creds, project = google.auth.default()\n",
    "  auth_req = google.auth.transport.requests.Request()\n",
    "  creds.refresh(auth_req)\n",
    "  access_token=creds.token\n",
    "\n",
    "  headers = {\n",
    "      \"Content-Type\" : \"application/json\",\n",
    "      \"Authorization\" : \"Bearer \" + access_token\n",
    "  }\n",
    "\n",
    "  model_version = \"imagen-3.0-generate-001\" # imagen-3.0-fast-generate-001\n",
    "  #model_version = \"imagen-3.0-generate-preview-0611\" # Preview Access Model\n",
    "\n",
    "  # https://cloud.google.com/vertex-ai/docs/generative-ai/model-reference/image-generation\n",
    "  # url = f\"https://{location}-aiplatform.googleapis.com/v1/projects/{project}/locations/{location}/publishers/google/models/imagegeneration:predict\"\n",
    "  url = f\"https://{location}-aiplatform.googleapis.com/v1/projects/{project}/locations/{location}/publishers/google/models/{model_version}:predict\"\n",
    "\n",
    "  payload = {\n",
    "    \"instances\": [\n",
    "      {\n",
    "        \"prompt\": prompt\n",
    "      }\n",
    "    ],\n",
    "    \"parameters\": {\n",
    "      \"sampleCount\": 1,\n",
    "      \"personGeneration\" : \"dont_allow\"  # change to allow_adult for people generation\n",
    "    }\n",
    "  }\n",
    "\n",
    "  response = requests.post(url, json=payload, headers=headers)\n",
    "\n",
    "  if response.status_code == 200:\n",
    "    response_json = json.loads(response.content)\n",
    "    # print(f\"Imagen3 response_json: {response_json}\")\n",
    "\n",
    "    if \"blocked\" in response_json:\n",
    "      print(f\"Blocked: {response_json['blocked']}\")\n",
    "\n",
    "    if \"predictions\" in response_json:\n",
    "      image_data = response_json[\"predictions\"][0][\"bytesBase64Encoded\"]\n",
    "      image_data = base64.b64decode(image_data)\n",
    "      filename= str(uuid.uuid4()) + \".png\"\n",
    "      with open(filename, \"wb\") as f:\n",
    "        f.write(image_data)\n",
    "      print(f\"Image generated OK.\")\n",
    "      return filename\n",
    "    else:\n",
    "      raise RuntimeError(f\"No predictions in response: {response.content}\")\n",
    "  else:\n",
    "    error = f\"Error with prompt:'{prompt}'  Status:'{response.status_code}' Text:'{response.text}'\"\n",
    "    raise RuntimeError(error)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ewLQxnZ0kl6e"
   },
   "source": [
    "#### Helper Functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "WQ-j3CTRlXo_"
   },
   "outputs": [],
   "source": [
    "def RunQuery(sql):\n",
    "  import time\n",
    "  from google.cloud import bigquery\n",
    "  client = bigquery.Client()\n",
    "\n",
    "  if (sql.startswith(\"SELECT\") or sql.startswith(\"WITH\")):\n",
    "      df_result = client.query(sql).to_dataframe()\n",
    "      return df_result\n",
    "  else:\n",
    "    job_config = bigquery.QueryJobConfig(priority=bigquery.QueryPriority.INTERACTIVE)\n",
    "    query_job = client.query(sql, job_config=job_config)\n",
    "\n",
    "    # Check on the progress by getting the job's updated state.\n",
    "    query_job = client.get_job(\n",
    "        query_job.job_id, location=query_job.location\n",
    "    )\n",
    "    print(\"Job {} is currently in state {} with error result of {}\".format(query_job.job_id, query_job.state, query_job.error_result))\n",
    "\n",
    "    while query_job.state != \"DONE\":\n",
    "      time.sleep(2)\n",
    "      query_job = client.get_job(\n",
    "          query_job.job_id, location=query_job.location\n",
    "          )\n",
    "      print(\"Job {} is currently in state {} with error result of {}\".format(query_job.job_id, query_job.state, query_job.error_result))\n",
    "\n",
    "    if query_job.error_result == None:\n",
    "      return True\n",
    "    else:\n",
    "      raise Exception(query_job.error_result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Y6gEjHLNKLGM"
   },
   "outputs": [],
   "source": [
    "def GetTableSchema(project_id, dataset_name, table_name):\n",
    "  import io\n",
    "  from google.cloud import bigquery\n",
    "\n",
    "  client = bigquery.Client()\n",
    "\n",
    "  dataset_ref = client.dataset(dataset_name, project=project_id)\n",
    "  table_ref = dataset_ref.table(table_name)\n",
    "  table = client.get_table(table_ref)\n",
    "\n",
    "  f = io.StringIO(\"\")\n",
    "  client.schema_to_json(table.schema, f)\n",
    "  return f.getvalue()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "tUWD4t8gKWO-"
   },
   "outputs": [],
   "source": [
    "def GetDistinctValues(project_id, dataset_name, table_name, field_name):\n",
    "  from google.cloud import bigquery\n",
    "\n",
    "  client = bigquery.Client()\n",
    "\n",
    "  sql = f\"\"\"\n",
    "  SELECT STRING_AGG(DISTINCT {field_name}, \",\" ) AS result\n",
    "    FROM `{project_id}.{dataset_name}.{table_name}`\n",
    "  \"\"\"\n",
    "\n",
    "  df_result = client.query(sql).to_dataframe()\n",
    "  result_str = df_result['result'].iloc[0]\n",
    "  if result_str is None:\n",
    "    return \"\"\n",
    "  else:\n",
    "    return result_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "zdofv6udKfmF"
   },
   "outputs": [],
   "source": [
    "def GetStartingValue(project_id, dataset_name, table_name, field_name):\n",
    "  from google.cloud import bigquery\n",
    "\n",
    "  client = bigquery.Client()\n",
    "\n",
    "  sql = f\"\"\"\n",
    "  SELECT IFNULL(MAX({field_name}),0) + 1 AS result\n",
    "    FROM `{project_id}.{dataset_name}.{table_name}`\n",
    "  \"\"\"\n",
    "\n",
    "  df_result = client.query(sql).to_dataframe()\n",
    "  return df_result['result'].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "WqMb9KPBVGLJ"
   },
   "outputs": [],
   "source": [
    "def GetMaxValue(project_id, dataset_name, table_name, field_name):\n",
    "  from google.cloud import bigquery\n",
    "\n",
    "  client = bigquery.Client()\n",
    "\n",
    "  sql = f\"\"\"\n",
    "  SELECT IFNULL(MAX({field_name}),0) AS result\n",
    "    FROM `{project_id}.{dataset_name}.{table_name}`\n",
    "  \"\"\"\n",
    "\n",
    "  df_result = client.query(sql).to_dataframe()\n",
    "  return df_result['result'].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ONcezrrfkplE"
   },
   "outputs": [],
   "source": [
    "def PrettyPrintJson(json_string):\n",
    "  json_object = json.loads(json_string)\n",
    "  json_formatted_str = json.dumps(json_object, indent=2)\n",
    "  #print(json_formatted_str)\n",
    "  return json_formatted_str"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "k3tXA3sDlbak"
   },
   "outputs": [],
   "source": [
    "# This was generated by GenAI\n",
    "\n",
    "def copy_file_to_gcs(local_file_path, bucket_name, destination_blob_name):\n",
    "  \"\"\"Copies a file from a local drive to a GCS bucket.\n",
    "\n",
    "  Args:\n",
    "      local_file_path: The full path to the local file.\n",
    "      bucket_name: The name of the GCS bucket to upload to.\n",
    "      destination_blob_name: The desired name of the uploaded file in the bucket.\n",
    "\n",
    "  Returns:\n",
    "      None\n",
    "  \"\"\"\n",
    "\n",
    "  import os\n",
    "  from google.cloud import storage\n",
    "\n",
    "  # Ensure the file exists locally\n",
    "  if not os.path.exists(local_file_path):\n",
    "      raise FileNotFoundError(f\"Local file '{local_file_path}' not found.\")\n",
    "\n",
    "  # Create a storage client\n",
    "  storage_client = storage.Client()\n",
    "\n",
    "  # Get a reference to the bucket\n",
    "  bucket = storage_client.bucket(bucket_name)\n",
    "\n",
    "  # Create a blob object with the desired destination path\n",
    "  blob = bucket.blob(destination_blob_name)\n",
    "\n",
    "  # Upload the file from the local filesystem\n",
    "  content_type = \"\"\n",
    "  if local_file_path.endswith(\".html\"):\n",
    "    content_type = \"text/html; charset=utf-8\"\n",
    "\n",
    "  if local_file_path.endswith(\".json\"):\n",
    "    content_type = \"application/json; charset=utf-8\"\n",
    "\n",
    "  if content_type == \"\":\n",
    "    blob.upload_from_filename(local_file_path)\n",
    "  else:\n",
    "    blob.upload_from_filename(local_file_path, content_type = content_type)\n",
    "\n",
    "  print(f\"File '{local_file_path}' uploaded to GCS bucket '{bucket_name}' as '{destination_blob_name}.  Content-Type: {content_type}'.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "NjoOh66Pld1z"
   },
   "source": [
    "### <font color='#4285f4'>BigQuery Tables</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "wSE1y7h9lgPk"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "CREATE OR REPLACE TABLE `${project_id}.${bigquery_chocolate_ai_dataset}.store`\n",
    "(\n",
    "    store_id INT64 NOT NULL OPTIONS(description=\"Primary key.\"),\n",
    "    store_name STRING NOT NULL OPTIONS(description=\"Name of the store\"),\n",
    "    store_address STRING NOT NULL OPTIONS(description=\"The address of the store\"),\n",
    "    store_latitude FLOAT64 NOT NULL OPTIONS(description=\"Latitude of the store\"),\n",
    "    store_longitude FLOAT64 NOT NULL OPTIONS(description=\"Longitude of the store\"),\n",
    ")\n",
    "CLUSTER BY store_id;\n",
    "\n",
    "INSERT INTO `${project_id}.${bigquery_chocolate_ai_dataset}.store`\n",
    "(store_id, store_name, store_address, store_latitude, store_longitude)\n",
    "VALUES\n",
    "(1, 'Rue Galande', '77 Rue Galande, 75005 Paris', 48.85206676782976, 2.3464926959635504),\n",
    "(2, 'Le Bon Marché', '24 Rue de Sèvres, 75007 Paris', 48.85082975134613, 2.324596734023611),\n",
    "(3, 'Square Louvois', '69 Rue de Richelieu, 75002 Paris', 48.86769158098546, 2.3376027993295176),\n",
    "(4, 'Av. des Champs-Élysées', '75 Av. des Champs-Élysées, 75008 Paris', 48.87101593967929, 2.302960997513936);\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Lz0KcwpPTO4M"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS `${project_id}.${bigquery_chocolate_ai_dataset}.order`\n",
    "(\n",
    "    order_id INTEGER NOT NULL OPTIONS(description=\"Primary key.\"),\n",
    "    store_id INTEGER NOT NULL OPTIONS(description=\"Foreign key: store table.\"),\n",
    "    customer_id INTEGER NOT NULL OPTIONS(description=\"Foreign key: Customer table.\"),\n",
    "    order_datetime TIMESTAMP NOT NULL OPTIONS(description=\"The datetime the order was started.\"),\n",
    "    order_completion_datetime TIMESTAMP NOT NULL OPTIONS(description=\"The datetime the order was completed.\")\n",
    ")\n",
    "CLUSTER BY order_id, store_id;\n",
    "\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS `${project_id}.${bigquery_chocolate_ai_dataset}.order_item`\n",
    "(\n",
    "    order_item_id INTEGER NOT NULL OPTIONS(description=\"Primary key.\"),\n",
    "    order_id INTEGER NOT NULL OPTIONS(description=\"Foreign key: Order table\"),\n",
    "    menu_id INTEGER NOT NULL OPTIONS(description=\"Foreign key: Menu table\"),\n",
    "    quantity INTEGER NOT NULL OPTIONS(description=\"Number of items ordered\"),\n",
    "    item_size STRING NOT NULL OPTIONS(description=\"Size of the item\"),\n",
    "    item_price FLOAT64 NOT NULL OPTIONS(description=\"Price of the item\"),\n",
    "    item_total FLOAT64 NOT NULL OPTIONS(description=\"Total price of the item\")\n",
    ")\n",
    "CLUSTER BY order_id;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "n9frOkAu-7pl"
   },
   "outputs": [],
   "source": [
    "def GetMenuItems():\n",
    "  sql = f\"\"\"SELECT TO_JSON(STRUCT(menu_name, menu_description)) AS menu_item_json\n",
    "  FROM `${project_id}.${bigquery_chocolate_ai_dataset}.menu`\"\"\"\n",
    "\n",
    "  result_df = RunQuery(sql)\n",
    "  result_str = \"\"\n",
    "\n",
    "  for index, row in result_df.iterrows():\n",
    "    result_str = result_str + row['menu_item_json'] + \",\"\n",
    "\n",
    "  return '[' + result_str + ']'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pnhsRHxckr2D"
   },
   "source": [
    "### <font color='#4285f4'>Generate Orders</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "DVjSx6iTWmJR"
   },
   "outputs": [],
   "source": [
    "max_store_id = GetMaxValue('${project_id}', dataset_name, \"store\", \"store_id\")\n",
    "max_customer_id = GetMaxValue('${project_id}', dataset_name, \"customer\", \"customer_id\")\n",
    "max_menu_id = GetMaxValue('${project_id}', dataset_name, \"menu\", \"menu_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "dPGP6PgCW21x"
   },
   "outputs": [],
   "source": [
    "for i in range(1,2):\n",
    "\n",
    "  sql = f\"\"\"INSERT INTO\n",
    "            `${project_id}.${bigquery_chocolate_ai_dataset}.order` (\n",
    "              order_id,\n",
    "              store_id,\n",
    "              customer_id,\n",
    "              order_datetime,\n",
    "              order_completion_datetime)\n",
    "\n",
    "          WITH\n",
    "            data_max_id AS (\n",
    "              SELECT IFNULL(MIN(order_datetime),CAST('2020-01-01' AS TIMESTAMP)) AS min_order_datetime,\n",
    "                    IFNULL(MAX(order_datetime),CAST('2020-01-01' AS TIMESTAMP)) AS max_order_datetime,\n",
    "                    IFNULL(MAX(order_id),0) AS max_id,\n",
    "                    COUNT(*) AS record_count\n",
    "                FROM`${project_id}.${bigquery_chocolate_ai_dataset}.order`\n",
    "            ),\n",
    "\n",
    "            data_random_data AS (\n",
    "              SELECT CAST(ROUND(1 + RAND() * ({max_store_id}     - 1)) AS INT64) AS store_id,\n",
    "                    CAST(ROUND(1 + RAND() * ({max_customer_id} - 1)) AS INT64) AS customer_id,\n",
    "                    TIMESTAMP_ADD(data_max_id.max_order_datetime, INTERVAL CAST(ROUND(1 + RAND() * (30 * 24 * 60 * 74) - 1) AS INT64) MINUTE) AS order_datetime, -- 74 months of minutes\n",
    "                FROM UNNEST(GENERATE_ARRAY(1, 1000000)) AS element\n",
    "                    CROSS JOIN data_max_id\n",
    "            ),\n",
    "\n",
    "            data_random_all_data AS (\n",
    "              SELECT *,\n",
    "                    TIMESTAMP_ADD(order_datetime, INTERVAL CAST(ROUND(60 + RAND() * ((60*15)) - 60) AS INT64) SECOND) AS order_completion_datetime, -- from 60 seconds to 15 minutes\n",
    "                FROM data_random_data\n",
    "            ),\n",
    "\n",
    "            data_ranked AS (\n",
    "              SELECT *,\n",
    "                    ROW_NUMBER() OVER (ORDER BY order_datetime) AS row_number\n",
    "              FROM data_random_all_data\n",
    "            )\n",
    "          SELECT row_number +  max_id AS  order_id,\n",
    "              store_id,\n",
    "              customer_id,\n",
    "              order_datetime,\n",
    "              order_completion_datetime\n",
    "          FROM data_ranked\n",
    "          CROSS JOIN data_max_id;\n",
    "  \"\"\"\n",
    "\n",
    "  RunQuery(sql)\n",
    "\n",
    "  print(f\"Order Loop {i} generated\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "vxbOnkHCXjVA"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT MIN(order_datetime),\n",
    "       MAX(order_completion_datetime)\n",
    " FROM `${project_id}.${bigquery_chocolate_ai_dataset}.order`;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Fiwut7bVXwoP"
   },
   "outputs": [],
   "source": [
    "# Create the order items\n",
    "\n",
    "sql = f\"\"\"INSERT INTO `${project_id}.${bigquery_chocolate_ai_dataset}.order_item` (order_item_id, order_id, menu_id, quantity, item_size, item_price, item_total)\n",
    "          WITH\n",
    "            data_max_id AS (\n",
    "              SELECT IFNULL(MAX(order_item_id),0) AS max_id\n",
    "                FROM `${project_id}.${bigquery_chocolate_ai_dataset}.order_item`\n",
    "            ),\n",
    "\n",
    "            -- order's without items\n",
    "            data_order AS (\n",
    "              SELECT order_id,\n",
    "                    CASE WHEN RAND() <= .5 THEN 1 -- most order are 1 item\n",
    "                          ELSE CAST(ROUND(2 + RAND() * (5 - 2)) AS INT64) -- up to 5 items per order\n",
    "                      END AS order_item_count\n",
    "                FROM `${project_id}.${bigquery_chocolate_ai_dataset}.order` AS order_t\n",
    "              WHERE NOT EXISTS (SELECT * FROM `${project_id}.${bigquery_chocolate_ai_dataset}.order_item` AS order_item WHERE order_t.order_id = order_item.order_id)\n",
    "            ),\n",
    "\n",
    "            data_order_with_array AS\n",
    "            (\n",
    "              select order_id,\n",
    "                    order_item_count,\n",
    "                    GENERATE_ARRAY(1, order_item_count) as order_item_array,\n",
    "                    RAND() AS quantity_rand\n",
    "                from data_order\n",
    "            ),\n",
    "\n",
    "            data_random_all_data AS (\n",
    "              SELECT order_id,\n",
    "                    order_item AS order_item_id,\n",
    "                    CAST(ROUND(1 + RAND() * (250 - 1)) AS INT64) AS menu_id,\n",
    "                    CASE WHEN quantity_rand <= .75 THEN 1 -- most orders are 1 quanity\n",
    "                        WHEN quantity_rand <= .85 THEN 2\n",
    "                        WHEN quantity_rand <= .90 THEN 3\n",
    "                        WHEN quantity_rand <= .95 THEN 4\n",
    "                        ELSE 5\n",
    "                      END AS quantity,\n",
    "                from data_order_with_array\n",
    "                    CROSS JOIN UNNEST(order_item_array) AS order_item\n",
    "            ),\n",
    "\n",
    "            data_ranked AS (\n",
    "              SELECT *,\n",
    "                    ROW_NUMBER() OVER (ORDER BY order_id, order_item_id) AS row_number\n",
    "              FROM data_random_all_data\n",
    "            )\n",
    "          SELECT row_number +  max_id AS order_item_id,\n",
    "                data_ranked.order_id,\n",
    "                data_ranked.menu_id,\n",
    "                data_ranked.quantity,\n",
    "                menu.menu_size,\n",
    "                menu.menu_price,\n",
    "                ROUND(CAST(data_ranked.quantity * menu.menu_price AS NUMERIC), 2, \"ROUND_HALF_EVEN\") AS item_total\n",
    "            FROM data_ranked\n",
    "                CROSS JOIN data_max_id\n",
    "                INNER JOIN `${project_id}.${bigquery_chocolate_ai_dataset}.menu` AS menu\n",
    "                        ON data_ranked.menu_id = menu.menu_id;\"\"\"\n",
    "\n",
    "\n",
    "RunQuery(sql)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5pfAK_MxWWnT"
   },
   "source": [
    "### <font color='#4285f4'>Verify Orders Items</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ffa6IVhNaQBp"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT COUNT(*) AS cnt\n",
    "  FROM `${project_id}.${bigquery_chocolate_ai_dataset}.order` AS parent\n",
    "WHERE NOT EXISTS (SELECT 1 FROM `${project_id}.${bigquery_chocolate_ai_dataset}.order_item` AS child WHERE parent.order_id = child.order_id);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "hzDJAAXKZWZR"
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT avg(item_total)\n",
    "  from  `${project_id}.${bigquery_chocolate_ai_dataset}.order` as parent\n",
    "      inner join `${project_id}.${bigquery_chocolate_ai_dataset}.order_item` as child on parent.order_id = child.order_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "FIORnEg5kuNd"
   },
   "source": [
    "### <font color='#4285f4'>Clean Up</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "xZgYyEP9kuhj"
   },
   "outputs": [],
   "source": [
    "# Placeholder"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "BJHuX_CwkuzM"
   },
   "source": [
    "### <font color='#4285f4'>Reference Links</font>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4eD2DhqttX_5"
   },
   "source": [
    "- [Google.com](https://www.google.com)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [
    "W3Q8_NLjj3Xw",
    "uSra7USNj3Xx",
    "DszuLZoo9A7k",
    "0mSjSjjylFnR",
    "be6n7F9IkdbT",
    "UCSP64xskgje",
    "vbNakk0fkjTj",
    "RtE_Q-H6lJQT",
    "NjoOh66Pld1z",
    "FIORnEg5kuNd",
    "BJHuX_CwkuzM"
   ],
   "name": "DB-GMA-Synthetic-Data-Generation-Orders.ipynb",
   "private_outputs": true,
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}