docs/docs.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "expired-halloween",
   "metadata": {},
   "source": [
    "# GCPy: Google Cloud ♥ Python \n",
    "\n",
    "A Python package to easily interface with Google Cloud Platform.\n",
    "\n",
    "## Instructions for installation\n",
    "1. Install gcloud SDK from [Link](https://cloud.google.com/sdk/docs/install)\n",
    "2. Initialize gloud SDK: `gcloud init`\n",
    "3. Authenticate your account: `gcloud auth application-default login`\n",
    "4. Clone this repo: `git clone https://github.com/stephenleo/gcpy.git`\n",
    "5. CD into the directory: `cd gcpy`\n",
    "6. Install: `python setup.py install`\n",
    "\n",
    "## Usage\n",
    "Import the package"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "talented-darwin",
   "metadata": {},
   "outputs": [],
   "source": [
    "import gcpy"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "positive-conditions",
   "metadata": {},
   "source": [
    "# query_to_gcs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "assigned-maker",
   "metadata": {},
   "source": [
    "Runs an SQL query on a Big Query table and stores the result into sharded csv files on a GCS bucket.\n",
    "\n",
    "**Important!** Remember to create the GCS bucket you want to use before running\n",
    "\n",
    "**Arguments:**\n",
    "- `sql_query_file`: Full path to the .sql file that contains the query. \n",
    "\n",
    "    Any query parameters should be within {}. eg: `WHERE col_name<{some_key}`. See query_params argument.\n",
    "    \n",
    "- `target_gcs_path`: GCS bucket path to store the output of the query in sharded csvs\n",
    "\n",
    "- `query_params`: Query Parameters dictionary. Default: `{}`\n",
    "\n",
    "    The sql query in .sql is treated as a string with parameters inside {key} replaced their corresponding values. \n",
    "    \n",
    "    eg: `{'some_key':10}` will change the sql: `WHERE col_name<{some_key}` --> `WHERE col_name<10`\n",
    "    \n",
    "- `project`: GCP Project name. Default: `CLIENT.project`\n",
    "\n",
    "- `dataset`: Big Query Dataset to temporarily store the results before moving to GCS. Default: `'gcpy'`\n",
    "\n",
    "    It is the string that appears inbetween the project name and table name in Big Query.\n",
    "        \n",
    "- `del_temp_bq_table`: Switch to delete the temporary BQ table. Default: `True`. \n",
    "\n",
    "    If set to `False`, the query results are saved in BQ table `f'{project}.{dataset}.{sql_query_file.split(\"/\")[-1].split(\".\")[0]}_{current_date_time}'`\n",
    "\n",
    "- `location`: location of the GCS bucket. Default: `'US'`\n",
    "\n",
    "- `client`: The BigQuery Client. Default: `CLIENT`\n",
    "\n",
    "**Returns:**\n",
    "The string path to the stored sharded csvs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "radical-literacy",
   "metadata": {},
   "source": [
    "## Example 1: Without `query_params`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "breathing-mouse",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "# Write your SQL query into a .sql file.\n",
    "echo \"\"\"SELECT\n",
    "  DISTINCT name,\n",
    "  gender\n",
    "FROM\n",
    "  bigquery-public-data.usa_names.usa_1910_current\"\"\" > name_gender.sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "stretch-vessel",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-02-07 23:18:26.729 INFO:\tExecuting query\n",
      "2021-02-07 23:18:30.079 INFO:\tQuery Execution time: 3.3476343154907227\n",
      "2021-02-07 23:18:30.080 INFO:\tWrite to Sharded CSVs in GCS\n",
      "2021-02-07 23:18:32.232 INFO:\tExported leo-gcp-sanbox.gcpy.name_gender_20210207231826 to gs://gcpy-bucket/us_name_gender/20210207231826/name_gender_*.csv\n",
      "2021-02-07 23:18:32.233 INFO:\tTime elapsed: 2.1513211727142334 seconds\n",
      "2021-02-07 23:18:32.554 INFO:\tDeleted leo-gcp-sanbox.gcpy.name_gender_20210207231826 temporary BQ Table\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query results saved in: gs://gcpy-bucket/us_name_gender/20210207231826/name_gender_*.csv\n"
     ]
    }
   ],
   "source": [
    "# Run your SQL query and save the results to your GCS bucket\n",
    "result_path = gcpy.query_to_gcs(sql_query_file='name_gender.sql', \n",
    "                                target_gcs_path='gs://gcpy-bucket/us_name_gender')\n",
    "\n",
    "print(f'Query results saved in: {result_path}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "infinite-straight",
   "metadata": {},
   "source": [
    "## Example 2: With `query_params`\n",
    "`query_params` argument can be used to control the SQL query at runtime.\n",
    "\n",
    "The below code will query all the names that start with `'A'`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "divided-business",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bash\n",
    "# Write your SQL query into a .sql file.\n",
    "echo \"\"\"SELECT\n",
    "  DISTINCT name,\n",
    "  gender\n",
    "FROM\n",
    "  bigquery-public-data.usa_names.usa_1910_current\n",
    "WHERE\n",
    "  name LIKE '{start_letter}%'\"\"\" > name_gender_start_letter.sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "floral-signal",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-02-07 23:18:35.717 INFO:\tExecuting query\n",
      "2021-02-07 23:18:38.156 INFO:\tQuery Execution time: 2.4370853900909424\n",
      "2021-02-07 23:18:38.156 INFO:\tWrite to Sharded CSVs in GCS\n",
      "2021-02-07 23:18:40.831 INFO:\tExported leo-gcp-sanbox.gcpy.name_gender_start_letter_20210207231835 to gs://gcpy-bucket/us_name_gender/20210207231835/name_gender_start_letter_*.csv\n",
      "2021-02-07 23:18:40.832 INFO:\tTime elapsed: 2.674234628677368 seconds\n",
      "2021-02-07 23:18:41.130 INFO:\tDeleted leo-gcp-sanbox.gcpy.name_gender_start_letter_20210207231835 temporary BQ Table\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query results saved in: gs://gcpy-bucket/us_name_gender/20210207231835/name_gender_start_letter_*.csv\n"
     ]
    }
   ],
   "source": [
    "# Run your SQL query and save the results to your GCS bucket\n",
    "result_path = gcpy.query_to_gcs(sql_query_file='name_gender_start_letter.sql', \n",
    "                                target_gcs_path='gs://gcpy-bucket/us_name_gender', \n",
    "                                query_params = {'start_letter': 'A'})\n",
    "\n",
    "print(f'Query results saved in: {result_path}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "reported-needle",
   "metadata": {},
   "source": [
    "# sharded_gcs_csv_to_pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "regulated-glenn",
   "metadata": {},
   "source": [
    "Loads sharded csv files on a GCS bucket into a single Pandas dataframe.\n",
    "\n",
    "**Arguments:**\n",
    "- `source_gcs_path`: source GCS path containing the sharded csv files to load to BQ\n",
    "\n",
    "- `file_prefix`: A file name prefix to select only the files of interest\n",
    "\n",
    "\n",
    "**Returns:**\n",
    "A single pandas dataframe that concatenates all the sharded csvs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "metallic-effectiveness",
   "metadata": {},
   "source": [
    "## Example\n",
    "Load the sharded csv files that were queried in the `query_to_gcs` example into a dataframe `df`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "expired-professor",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 1/1 [00:01<00:00,  1.64s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3804, 2)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alma</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Antoinette</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Anica</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ayah</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Anaya</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name gender\n",
       "0        Alma      F\n",
       "1  Antoinette      F\n",
       "2       Anica      F\n",
       "3        Ayah      F\n",
       "4       Anaya      F"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = gcpy.sharded_gcs_csv_to_pd(source_gcs_path='gs://gcpy-bucket/us_name_gender/20210207231835/', \n",
    "                                file_prefix='name_gender_')\n",
    "\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "appointed-obligation",
   "metadata": {},
   "source": [
    "# pd_to_bq"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "painful-fundamental",
   "metadata": {},
   "source": [
    "Load a Pandas Dataframe into a BigQuery table\n",
    "    \n",
    "**Arguments:**\n",
    "- `source_df`: The DataFrame to load into BQ\n",
    "\n",
    "- `target_dataset`: BQ dataset containing the `target_tablename` table to import into\n",
    "\n",
    "- `target_tablename`: BQ table to import into\n",
    "\n",
    "- `project`: GCP Project name. Default: `CLIENT.project`\n",
    "\n",
    "- `client`: The BigQuery Client. Default: `CLIENT`\n",
    "\n",
    "**Returns:**\n",
    "The string BQ table name where source_df has been uploaded to"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "rolled-dressing",
   "metadata": {},
   "source": [
    "## Example\n",
    "Load the pandas dataframe `df` into a BQ table `f'{project}.{target_dataset}.{target_tablename}'`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "roman-experiment",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-02-07 23:19:05.687 INFO:\t<google.cloud.bigquery.job.load.LoadJob object at 0x7f96b5944730>\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Source Dataframe uploaded to BQ table: leo-gcp-sanbox.gcpy.us_name_gender\n"
     ]
    }
   ],
   "source": [
    "result_table = gcpy.pd_to_bq(source_df=df, target_dataset='gcpy', target_tablename='us_name_gender')\n",
    "\n",
    "print(f'Source Dataframe uploaded to BQ table: {result_table}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bizarre-adrian",
   "metadata": {},
   "source": [
    "# gcs_to_bq"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "infectious-application",
   "metadata": {},
   "source": [
    "Load sharded csvs from GCS into BQ\n",
    "\n",
    "**Arguments:**\n",
    "- `source_gcs_path`: source GCS path containing the files to load to BQ\n",
    "\n",
    "- `target_bq_dataset`: BQ dataset containing the `\"target_bq_tablename\"` table to import into\n",
    "\n",
    "- `target_bq_tablename`: BQ table to import into\n",
    "\n",
    "- `target_table_schema`: `'auto'` or dictionary of the form `{'col_name': 'dtype'}`. Default: `'auto'`\n",
    "\n",
    "- `num_header_rows`: Number of header rows to skip while data upload. Default: `1` will skip the first row (column header)\n",
    "\n",
    "- `project`: GCP Project name. Default: `CLIENT.project`\n",
    "\n",
    "- `client`: The BigQuery Client. Default: `CLIENT`\n",
    "\n",
    "**Returns:**\n",
    "The string BQ table name where csv files from source_gcs_path has been uploaded to"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "medieval-stable",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2021-02-07 23:19:59.517 INFO:\tStarting job ceb45ecb-1fa5-4614-964d-522540a48b74\n",
      "2021-02-07 23:20:02.004 INFO:\tJob finished. Time elapsed: 2.944720506668091 seconds\n",
      "2021-02-07 23:20:02.297 INFO:\tLoaded 3804 rows\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CSV files in source GCS path uploaded to BQ table: leo-gcp-sanbox.gcpy.us_name_gender_2\n"
     ]
    }
   ],
   "source": [
    "result_table = gcpy.gcs_to_bq(source_gcs_path='gs://gcpy-bucket/us_name_gender/20210207231835/*.csv', \n",
    "                              target_bq_dataset='gcpy', target_bq_tablename='us_name_gender_2', \n",
    "                              target_table_schema = {\"name\": \"string\", \"gender\": \"string\"})\n",
    "\n",
    "print(f'CSV files in source GCS path uploaded to BQ table: {result_table}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "desperate-thumb",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}