def create_table(self, bq_table, project_id, dataset_id, table_id):
"""
Create a table with the given schema in BigQuery.
:param bq_table: BQTable object
:param project_id: project id
:param dataset_id: dataset id
:param table_id: table id
:return: True if successful otherwise False
"""
bq_schema = bq_table.get_schema()
tf = tempfile.NamedTemporaryFile(delete=False)
tf.write(bq_schema.to_json())
tf.close()
# bq mk --table --expiration [INTEGER] --description [DESCRIPTION]
# --label [KEY:VALUE, KEY:VALUE] [PROJECT_ID]:[DATASET].[TABLE] [SCHEMA]
args = '{0}:{1}.{2} {3}'.format(project_id, dataset_id, table_id,
tf.name)
cflags = '--table --label organization:rdr'
pcode, so, se = gcp_bq_command('mk', args=args, command_flags=cflags) # pylint: disable=unused-variable
os.unlink(tf.name)
if pcode != 0:
if 'parsing error in row starting at position' in so:
raise BQInvalidSchemaException(so)
else:
raise BQException(se if se else so)
_logger.info(' {0}: {1}'.format(
'{0}.{1}.{2}'.format(project_id, dataset_id,
table_id).ljust(LJUST_WIDTH, '.'), 'created'))
return True
def get_table_schema(self, project_id, dataset_id, table_id):
"""
Retrieve the table schema from BigQuery
:param project_id: project id
:param dataset_id: dataset id
:param table_id: table id
:return: string
"""
# bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE]
args = '{0}:{1}.{2}'.format(project_id, dataset_id, table_id)
pcode, so, se = gcp_bq_command(
'show', args=args, command_flags='--schema --format=prettyjson') # pylint: disable=unused-variable
if pcode != 0:
if 'Not found' in so:
return None
if 'Authorization error' in so:
_logger.error(
'** BigQuery returned an authorization error, please check the following: **'
)
_logger.error(' * Service account has correct permissions.')
_logger.error(
' * Timezone and time on computer match PMI account settings.'
)
# for more suggestions look at:
# https://blog.timekit.io/google-oauth-invalid-grant-nightmare-and-how-to-fix-it-9f4efaf1da35
raise BQException(se if se else so)
return so
def delete_view(self, bq_view, project_id, dataset_id, view_id):
"""
Delete the view from BigQuery
:param bq_view: BQView object
:param project_id: project id
:param dataset_id: dataset id
:param view_id: table id
:return: string
"""
bq_table = bq_view.get_table()
if not bq_table:
raise ValueError(
'BQView {0} does not have a BQTable object configured.')
# bq rm --force --table [PROJECT_ID]:[DATASET].[TABLE]
args = '{0}:{1}.{2}'.format(project_id, dataset_id, view_id)
pcode, so, se = gcp_bq_command('rm',
args=args,
command_flags='--force --table') # pylint: disable=unused-variable
if pcode != 0:
raise BQException(se if se else so)
_logger.info(' {0}: {1}'.format(
'{0}.{1}.{2}'.format(project_id, dataset_id,
view_id).ljust(75, '.'), 'deleted'))
return so
def create_view(self, bq_view, project_id, dataset_id, view_id):
"""
Create a view
:param bq_view: BQView object
:param project_id: project id
:param dataset_id: dataset id
:param view_id: table id
:return: True if successful otherwise False
"""
view_desc = bq_view.get_descr()
view_sql = bq_view.get_sql()
bq_table = bq_view.get_table()
if not bq_table:
raise ValueError(
'BQView {0} does not have a BQTable object configured.')
tmp_sql = view_sql.format(project=project_id, dataset=dataset_id)
args = '{0}:{1}.{2}'.format(project_id, dataset_id, view_id)
# Try to update
cflags = "--description '{0}' --view '{1}'".format(view_desc, tmp_sql)
pcode, so, se = gcp_bq_command('update',
args=args,
command_flags=cflags) # pylint: disable=unused-variable
if pcode == 0:
_logger.info(' {0}: {1}'.format(
'{0}.{1}.{2}'.format(project_id, dataset_id,
view_id).ljust(LJUST_WIDTH, '.'),
'updated'))
else:
cflags = "--use_legacy_sql=false --label organization:rdr --description '{0}' --view '{1}'".\
format(view_desc, tmp_sql)
pcode, so, se = gcp_bq_command('mk',
args=args,
command_flags=cflags) # pylint: disable=unused-variable
if pcode != 0:
raise BQException(se if se else so)
_logger.info(' {0}: {1}'.format(
'{0}.{1}.{2}'.format(project_id, dataset_id,
view_id).ljust(LJUST_WIDTH, '.'),
'created'))
return True
def delete_table(self, project_id, dataset_id, table_id):
"""
Delete the table from BigQuery
:param project_id: project id
:param dataset_id: dataset id
:param table_id: table id
:return: String
"""
# bq rm --force --table [PROJECT_ID]:[DATASET].[TABLE]
args = '{0}:{1}.{2}'.format(project_id, dataset_id, table_id)
pcode, so, se = gcp_bq_command('rm',
args=args,
command_flags='--force --table') # pylint: disable=unused-variable
if pcode != 0:
raise BQException(se if se else so)
_logger.info(' {0}: {1}'.format(
'{0}.{1}.{2}'.format(project_id, dataset_id,
table_id).ljust(LJUST_WIDTH, '.'), 'deleted'))
return so
def modify_table(self, bq_table, project_id, dataset_id, table_id):
"""
Modify the schema of a table in BigQuery.
:param bq_table: BQTable object
:param project_id: project id
:param dataset_id: dataset id
:param table_id: table id
:return: True if successful otherwise False
"""
bq_schema = bq_table.get_schema()
tf = tempfile.NamedTemporaryFile(delete=False)
tf.write(bq_schema.to_json())
tf.close()
# bq update [PROJECT_ID]:[DATASET].[TABLE] [SCHEMA]
args = '{0}:{1}.{2} {3}'.format(project_id, dataset_id, table_id,
tf.name)
pcode, so, se = gcp_bq_command('update', args=args) # pylint: disable=unused-variable
os.unlink(tf.name)
if pcode == 0:
_logger.info(' {0}: {1}'.format(
'{0}.{1}.{2}'.format(project_id, dataset_id,
table_id).ljust(LJUST_WIDTH, '.'),
'updated'))
else:
if 'already exists in schema' in so:
raise BQDuplicateFieldException(so)
elif 'parsing error in row starting at position' in so:
raise BQInvalidSchemaException(so)
elif 'add required columns to an existing schema' in so:
raise BQInvalidModeException(so)
elif 'Precondition Failed' in so:
raise BQSchemaStructureException(so)
else:
raise BQException(so)
return True