Skip to content

Instantly share code, notes, and snippets.

@ernestoongaro

ernestoongaro/adding-service-account-key-for-bigquery.ipynb

Last active February 17, 2023 17:21
Show Gist options
  • Save ernestoongaro/6b28864e9ca704914c9a22beef3b41e2 to your computer and use it in GitHub Desktop.
Save ernestoongaro/6b28864e9ca704914c9a22beef3b41e2 to your computer and use it in GitHub Desktop.
Download ZIP
adding-service-account-key-for-bigquery.ipynb
Display the source blob
Display the rendered blob
Raw
adding-service-account-key-for-bigquery.ipynb
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"provenance": [],
"authorship_tag": "ABX9TyPLCY4SklpyUY73Q48qhEHF",
"include_colab_link": true
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
},
"language_info": {
"name": "python"
}
},
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/ernestoongaro/6b28864e9ca704914c9a22beef3b41e2/adding-service-account-key-for-bigquery.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"cell_type": "markdown",
"source": [
"## What is this for?\n",
"An example of \n",
"* Logging into GCP with a service account\n",
"* Createing a service account Key\n",
"* Uploading it to dbt Cloud (replacing the one in a project)"
],
"metadata": {
"id": "H6h3JOPHEW07"
}
},
{
"cell_type": "code",
"source": [
"from getpass import getpass\n",
"import os\n",
"from google.auth.transport import requests\n",
"import googleapiclient.discovery\n",
"import time\n",
"import json\n",
"import base64\n",
"import requests\n",
"\n",
"os.environ[\"DBT_CLOUD_API_TOKEN\"] = getpass('API TOKEN:') #don't edit this - enter interactively\n",
"os.environ[\"DBT_CLOUD_ACCOUNT_ID\"] = '1'\n",
"os.environ[\"DBT_CLOUD_PROJECT_ID\"] = '56'\n",
"os.environ[\"DBT_CLOUD_CONNECTION_ID\"] = '374'\n",
"os.environ[\"DBT_CLOUD_HOST\"] = 'emea.dbt.com'\n",
"\n",
"#service account email:\n",
"os.environ[\"SERVICE_ACCOUNT_EMAIL\"] = 'emea-demo@sales-demo-project-314714.iam.gserviceaccount.com'\n",
"\n"
],
"metadata": {
"id": "YAQnAlz3NsRJ",
"outputId": "c66e8d2a-7c34-4cff-ce56-26019bd873da",
"colab": {
"base_uri": "https://localhost:8080/"
}
},
"execution_count": 16,
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"API TOKEN:··········\n"
]
}
]
},
{
"cell_type": "code",
"source": [
"#authenticate to GCP using service account \n",
"def authenticate_to_gcp_with_service_account():\n",
" from google.oauth2.credentials import Credentials\n",
" from google.oauth2 import service_account\n",
" from google.colab import files\n",
"\n",
" service_account_file = files.upload()\n",
"\n",
" file_name = list(service_account_file.keys())[0]\n",
"\n",
" creds = service_account.Credentials.from_service_account_file(file_name,\n",
" scopes=['https://www.googleapis.com/auth/cloud-platform'])\n",
" return creds\n",
"\n"
],
"metadata": {
"id": "LwRWXAZ1GXdi"
},
"execution_count": 21,
"outputs": []
},
{
"cell_type": "code",
"source": [
"#Creates a key for a service account https://cloud.google.com/iam/docs/creating-managing-service-account-keys#iam-service-account-keys-get-rest\n",
"\n",
"def create_service_account_key(creds):\n",
" service = googleapiclient.discovery.build(\n",
" 'iam', 'v1', credentials=creds)\n",
"\n",
" key = service.projects().serviceAccounts().keys().create(\n",
" name='projects/-/serviceAccounts/' + os.environ['SERVICE_ACCOUNT_EMAIL'], body={}\n",
" ).execute()\n",
"\n",
" # Get the private key data\n",
" key_data = json.loads(base64.b64decode(key['privateKeyData']).decode('utf-8'))\n",
" gcp_project_id = key_data[\"project_id\"]\n",
" gcp_private_key = key_data[\"private_key\"]\n",
" gcp_key_id = key_data[\"private_key_id\"]\n",
"\n",
" return {\n",
" \"key_data\": key_data,\n",
" \"gcp_project_id\": gcp_project_id,\n",
" \"gcp_private_key\": gcp_private_key,\n",
" \"gcp_key_id\": gcp_key_id\n",
" }"
],
"metadata": {
"id": "epH9IA1M0HZ1"
},
"execution_count": 26,
"outputs": []
},
{
"cell_type": "code",
"source": [
"#upload the key to dbt Cloud\n",
"def update_dbt_cloud_key(key):\n",
" key_data = key[\"key_data\"]\n",
" gcp_project_id = key[\"gcp_project_id\"]\n",
" gcp_private_key = key[\"gcp_private_key\"]\n",
" gcp_key_id = key[\"gcp_key_id\"]\n",
"\n",
"\n",
" res = requests.post(\n",
" url=f\"https://{host}/api/v3/accounts/{account_id}/projects/{project_id}/connections/{connection_id}/\",\n",
" headers={'Authorization': f\"Token {api_token}\"},\n",
" json={\n",
" \"id\": 374,\n",
" \"type\": \"bigquery\",\n",
" \"account_id\": account_id,\n",
" \"project_id\": project_id,\n",
" \"created_by_id\": 19,\n",
" \"name\": \"BigQuery\",\n",
" \"state\": 1,\n",
" \"details\": {\n",
" \"retries\": 1,\n",
" \"location\": None,\n",
" \"maximum_bytes_billed\": 0,\n",
" \"timeout_seconds\": 300,\n",
" \"project_id\": \"sales-demo-project-314714\",\n",
" \"private_key_id\": gcp_key_id,\n",
" \"private_key\": gcp_private_key,\n",
" \"client_email\": service_account_email,\n",
" \"client_id\": \"107958868107460349218\",\n",
" \"auth_uri\": \"https://accounts.google.com/o/oauth2/auth\",\n",
" \"token_uri\": \"https://oauth2.googleapis.com/token\",\n",
" \"auth_provider_x509_cert_url\": \"https://www.googleapis.com/oauth2/v1/certs\",\n",
" \"client_x509_cert_url\": \"https://www.googleapis.com/robot/v1/metadata/x509/emea-demo%40sales-demo-project-314714.iam.gserviceaccount.com\",\n",
" \"gcs_bucket\": None,\n",
" \"dataproc_region\": None,\n",
" \"dataproc_cluster_name\": None,\n",
" \"application_id\": None,\n",
" \"application_secret\": None,\n",
" \"scopes\": [\n",
" \"https://www.googleapis.com/auth/bigquery\",\n",
" \"https://www.googleapis.com/auth/cloud-platform\",\n",
" \"https://www.googleapis.com/auth/drive\"\n",
" ]\n",
" }\n",
" }\n",
" )\n",
" \n",
"\n",
" print(res.request.url)\n",
" print(res.request.body)\n",
" print(res.request.headers)\n",
"\n",
" try:\n",
" res.raise_for_status()\n",
" except:\n",
" raise\n"
],
"metadata": {
"id": "qsm0ZUYFMkaF"
},
"execution_count": 23,
"outputs": []
},
{
"cell_type": "code",
"source": [
"creds = authenticate_to_gcp_with_service_account()\n",
"key = create_service_account_key(creds)\n",
"update_dbt_cloud_key(key)"
],
"metadata": {
"id": "ugGGkYCQNNG-",
"outputId": "8cc3d1da-3c63-40bc-9a2e-b3ad812a3658",
"colab": {
"base_uri": "https://localhost:8080/",
"height": 378
}
},
"execution_count": 25,
"outputs": [
{
"output_type": "display_data",
"data": {
"text/plain": [
"<IPython.core.display.HTML object>"
],
"text/html": [
"\n",
" <input type=\"file\" id=\"files-58f69b88-81e9-4e9d-a310-e0eea8d5a880\" name=\"files[]\" multiple disabled\n",
" style=\"border:none\" />\n",
" <output id=\"result-58f69b88-81e9-4e9d-a310-e0eea8d5a880\">\n",
" Upload widget is only available when the cell has been executed in the\n",
" current browser session. Please rerun this cell to enable.\n",
" </output>\n",
" <script>// Copyright 2017 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",
"// http://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",
"\n",
"/**\n",
" * @fileoverview Helpers for google.colab Python module.\n",
" */\n",
"(function(scope) {\n",
"function span(text, styleAttributes = {}) {\n",
" const element = document.createElement('span');\n",
" element.textContent = text;\n",
" for (const key of Object.keys(styleAttributes)) {\n",
" element.style[key] = styleAttributes[key];\n",
" }\n",
" return element;\n",
"}\n",
"\n",
"// Max number of bytes which will be uploaded at a time.\n",
"const MAX_PAYLOAD_SIZE = 100 * 1024;\n",
"\n",
"function _uploadFiles(inputId, outputId) {\n",
" const steps = uploadFilesStep(inputId, outputId);\n",
" const outputElement = document.getElementById(outputId);\n",
" // Cache steps on the outputElement to make it available for the next call\n",
" // to uploadFilesContinue from Python.\n",
" outputElement.steps = steps;\n",
"\n",
" return _uploadFilesContinue(outputId);\n",
"}\n",
"\n",
"// This is roughly an async generator (not supported in the browser yet),\n",
"// where there are multiple asynchronous steps and the Python side is going\n",
"// to poll for completion of each step.\n",
"// This uses a Promise to block the python side on completion of each step,\n",
"// then passes the result of the previous step as the input to the next step.\n",
"function _uploadFilesContinue(outputId) {\n",
" const outputElement = document.getElementById(outputId);\n",
" const steps = outputElement.steps;\n",
"\n",
" const next = steps.next(outputElement.lastPromiseValue);\n",
" return Promise.resolve(next.value.promise).then((value) => {\n",
" // Cache the last promise value to make it available to the next\n",
" // step of the generator.\n",
" outputElement.lastPromiseValue = value;\n",
" return next.value.response;\n",
" });\n",
"}\n",
"\n",
"/**\n",
" * Generator function which is called between each async step of the upload\n",
" * process.\n",
" * @param {string} inputId Element ID of the input file picker element.\n",
" * @param {string} outputId Element ID of the output display.\n",
" * @return {!Iterable<!Object>} Iterable of next steps.\n",
" */\n",
"function* uploadFilesStep(inputId, outputId) {\n",
" const inputElement = document.getElementById(inputId);\n",
" inputElement.disabled = false;\n",
"\n",
" const outputElement = document.getElementById(outputId);\n",
" outputElement.innerHTML = '';\n",
"\n",
" const pickedPromise = new Promise((resolve) => {\n",
" inputElement.addEventListener('change', (e) => {\n",
" resolve(e.target.files);\n",
" });\n",
" });\n",
"\n",
" const cancel = document.createElement('button');\n",
" inputElement.parentElement.appendChild(cancel);\n",
" cancel.textContent = 'Cancel upload';\n",
" const cancelPromise = new Promise((resolve) => {\n",
" cancel.onclick = () => {\n",
" resolve(null);\n",
" };\n",
" });\n",
"\n",
" // Wait for the user to pick the files.\n",
" const files = yield {\n",
" promise: Promise.race([pickedPromise, cancelPromise]),\n",
" response: {\n",
" action: 'starting',\n",
" }\n",
" };\n",
"\n",
" cancel.remove();\n",
"\n",
" // Disable the input element since further picks are not allowed.\n",
" inputElement.disabled = true;\n",
"\n",
" if (!files) {\n",
" return {\n",
" response: {\n",
" action: 'complete',\n",
" }\n",
" };\n",
" }\n",
"\n",
" for (const file of files) {\n",
" const li = document.createElement('li');\n",
" li.append(span(file.name, {fontWeight: 'bold'}));\n",
" li.append(span(\n",
" `(${file.type || 'n/a'}) - ${file.size} bytes, ` +\n",
" `last modified: ${\n",
" file.lastModifiedDate ? file.lastModifiedDate.toLocaleDateString() :\n",
" 'n/a'} - `));\n",
" const percent = span('0% done');\n",
" li.appendChild(percent);\n",
"\n",
" outputElement.appendChild(li);\n",
"\n",
" const fileDataPromise = new Promise((resolve) => {\n",
" const reader = new FileReader();\n",
" reader.onload = (e) => {\n",
" resolve(e.target.result);\n",
" };\n",
" reader.readAsArrayBuffer(file);\n",
" });\n",
" // Wait for the data to be ready.\n",
" let fileData = yield {\n",
" promise: fileDataPromise,\n",
" response: {\n",
" action: 'continue',\n",
" }\n",
" };\n",
"\n",
" // Use a chunked sending to avoid message size limits. See b/62115660.\n",
" let position = 0;\n",
" do {\n",
" const length = Math.min(fileData.byteLength - position, MAX_PAYLOAD_SIZE);\n",
" const chunk = new Uint8Array(fileData, position, length);\n",
" position += length;\n",
"\n",
" const base64 = btoa(String.fromCharCode.apply(null, chunk));\n",
" yield {\n",
" response: {\n",
" action: 'append',\n",
" file: file.name,\n",
" data: base64,\n",
" },\n",
" };\n",
"\n",
" let percentDone = fileData.byteLength === 0 ?\n",
" 100 :\n",
" Math.round((position / fileData.byteLength) * 100);\n",
" percent.textContent = `${percentDone}% done`;\n",
"\n",
" } while (position < fileData.byteLength);\n",
" }\n",
"\n",
" // All done.\n",
" yield {\n",
" response: {\n",
" action: 'complete',\n",
" }\n",
" };\n",
"}\n",
"\n",
"scope.google = scope.google || {};\n",
"scope.google.colab = scope.google.colab || {};\n",
"scope.google.colab._files = {\n",
" _uploadFiles,\n",
" _uploadFilesContinue,\n",
"};\n",
"})(self);\n",
"</script> "
]
},
"metadata": {}
},
{
"output_type": "stream",
"name": "stdout",
"text": [
"Saving gcp_key.json to gcp_key (5).json\n"
]
},
{
"output_type": "error",
"ename": "NameError",
"evalue": "ignored",
"traceback": [
"\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
"\u001b[0;31mNameError\u001b[0m Traceback (most recent call last)",
"\u001b[0;32m<ipython-input-25-d250e6598f6d>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0mcreds\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mauthenticate_to_gcp_with_service_account\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 2\u001b[0m \u001b[0mcreate_service_account_key\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcreds\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0mupdate_dbt_cloud_key\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
"\u001b[0;32m<ipython-input-23-cf3dc3f8268b>\u001b[0m in \u001b[0;36mupdate_dbt_cloud_key\u001b[0;34m()\u001b[0m\n\u001b[1;32m 18\u001b[0m \u001b[0;34m\"timeout_seconds\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;36m300\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 19\u001b[0m \u001b[0;34m\"project_id\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m\"sales-demo-project-314714\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 20\u001b[0;31m \u001b[0;34m\"private_key_id\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mgcp_key_id\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 21\u001b[0m \u001b[0;34m\"private_key\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mgcp_private_key\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 22\u001b[0m \u001b[0;34m\"client_email\"\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mservice_account_email\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
"\u001b[0;31mNameError\u001b[0m: name 'gcp_key_id' is not defined"
]
}
]
},
{
"cell_type": "code",
"source": [],
"metadata": {
"id": "9HYmEsmXQX4c"
},
"execution_count": null,
"outputs": []
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment