def generate_signed_url(blob):
"""Generate signed URL for storage blob"""
credentials = default(
scopes=["https://www.googleapis.com/auth/cloud-platform"])[0]
signer = iam.Signer(
request=requests.Request(),
credentials=credentials,
service_account_email=os.getenv("FUNCTION_IDENTITY"),
)
# Create token-based service account credentials for signing
signing_credentials = service_account.IDTokenCredentials(
signer=signer,
token_uri="https://www.googleapis.com/oauth2/v4/token",
target_audience="",
service_account_email=os.getenv("FUNCTION_IDENTITY"),
)
# Cloud Functions service account must have Service Account Token Creator role
try:
url = blob.generate_signed_url(
version="v4",
expiration=datetime.timedelta(
hours=int(get_env('SIGNED_URL_EXPIRATION'))),
method="GET",
credentials=signing_credentials)
except exceptions.TransportError:
logging.error(
RuntimeError("Service account running the function must have IAM "
"roles/iam.serviceAccountTokenCreator."))
else:
print("Generated signed URL.")
return url
def build_id_token_credentials(
self,
target_audience: str,
token_uri: Optional[str] = None,
) -> Tuple[IDTokenCredentials, Request]:
request = self._build_request()
id_token_credentials = self._fetch_id_token_credentials(
target_audience=target_audience)
try:
if not id_token_credentials or not id_token_credentials.valid:
logger.debug(
'Build service_account.IDTokenCredentials and token refresh.'
)
_credential = self.build_credentials()
id_token_credentials = service_account.IDTokenCredentials(
signer=_credential.signer,
service_account_email=_credential.service_account_email,
token_uri=token_uri if token_uri else DEFAULT_TOKEN_URI,
target_audience=target_audience,
)
id_token_credentials.refresh(request=request)
self._cache_id_token_credentials[
target_audience] = id_token_credentials
return (id_token_credentials, request)
except ServiceAccountConfigurationError:
raise
except RuntimeError as e:
raise ServiceAccountConfigurationError(e)
def _get_id_credentials(self):
credentials = service_account.IDTokenCredentials(
signer=self.credentials.signer,
service_account_email=self.credentials.service_account_email,
token_uri=
"https://oauth2.googleapis.com/token", # TODO: fetch from credentials
target_audience=self.audience,
)
if not credentials.valid:
credentials.refresh(request=Request())
return credentials
def make_credentials(cls):
return service_account.IDTokenCredentials(SIGNER,
cls.SERVICE_ACCOUNT_EMAIL,
cls.TOKEN_URI,
cls.TARGET_AUDIENCE)
def main():
"""Sends an email with a Google Cloud Storage signed URL of BQ Query results.
Creates BQ table, runs a SQL query, and exports the results to Cloud Storage as a CSV.
Generates signing credentials for the CSV and sends an email with a link to the signed URL.
Args:
None
Returns:
None
"""
#Create BQ and Storage Client
bq_client = bigquery.Client(credentials=credentials())
storage_client = storage.Client(credentials=credentials())
# Set variables
timestr = time.strftime("%Y%m%d%I%M%S")
project = "report-scheduling"
dataset_id = "bq_exports"
file_name = "daily_export_" + timestr
csv_name = file_name + ".csv"
table_id = "report-scheduling.bq_exports." + file_name
bucket_name = "bq_email_exports"
from_email = "*****@*****.**"
to_emails = "*****@*****.**"
# Create a BQ table
schema = [
bigquery.SchemaField("url", "STRING", mode="REQUIRED"),
bigquery.SchemaField("view_count", "INTEGER", mode="REQUIRED"),
]
# Make an API request to create table
table = bq_client.create_table(bigquery.Table(table_id, schema=schema))
print("Created table {}.{}.{}".format(table.project, table.dataset_id,
table.table_id))
# Run query on that table
job_config = bigquery.QueryJobConfig(destination=table_id)
# Define the SQL query
sql = """
SELECT
CONCAT(
'https://stackoverflow.com/questions/',
CAST(id as STRING)) as url,
view_count
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE tags like '%google-bigquery%'
ORDER BY view_count DESC
LIMIT 10
"""
# Start the query, passing in the extra configuration
query_job = bq_client.query(sql, job_config=job_config)
# Wait for the job to complete
query_job.result()
print("Query results loaded to the table {}".format(table_id))
# Export table data as CSV to GCS
destination_uri = "gs://{}/{}".format(bucket_name, csv_name)
dataset_ref = bq_client.dataset(dataset_id, project=project)
table_ref = dataset_ref.table(file_name)
extract_job = bq_client.extract_table(
table_ref,
destination_uri,
# Location must match that of the source table
location="US",
)
# Waits for job to complete
extract_job.result()
print("Exported {}:{}.{} to {}".format(project, dataset_id, table_id,
destination_uri))
# Generate a v4 signed URL for downloading a blob
bucket = storage_client.bucket(bucket_name)
blob = bucket.blob(csv_name)
signing_credentials = None
# If running on GCF, generate signing credentials
# Service account running the GCF must have Service Account Token Creator role
if os.getenv("IS_LOCAL") is None:
signer = iam.Signer(
request=requests.Request(),
credentials=credentials(),
service_account_email=os.getenv("FUNCTION_IDENTITY"),
)
# Create Token-based service account credentials for signing
signing_credentials = service_account.IDTokenCredentials(
signer=signer,
token_uri="https://www.googleapis.com/oauth2/v4/token",
target_audience="",
service_account_email=os.getenv("FUNCTION_IDENTITY"),
)
url = blob.generate_signed_url(
version="v4",
# This URL is valid for 24 hours, until the next email
expiration=datetime.timedelta(hours=24),
# Allow GET requests using this URL
method="GET",
# Signing credentials; if None falls back to json credentials in local environment
credentials=signing_credentials,
)
print("Generated GET signed URL.")
# Create email message through SendGrid with link to signed URL
message = Mail(
from_email=from_email,
to_emails=to_emails,
subject="Daily BQ export",
html_content="<p> Your daily BigQuery export from Google Cloud Platform \
is linked <a href={}>here</a>.</p>".format(url),
)
# Send email
try:
sg = SendGridAPIClient(os.environ.get('SENDGRID_API_KEY'))
response = sg.send(message)
print(response.status_code)
except Exception as e:
print(e.message)