Data to Native table

The better way would be to directly create native tables from Google Cloud Storage itself. The external tables are read only, so there is no modification you could have made on the tables. But here I am giving the code for your use case i.e. move data from multiple external tables to native tables.

I am using the Python client library and hence it is required that you follow this document to install the library and set up authentication as mentioned, before running the code. In case you want to grant BigQuery specific roles to the service account, you will also need to add storage.objectViewer role to your service account as well, since we are querying external tables. My external tables (along with many other tables) are placed in dataset “Dataset1” and I am copying them to another dataset “Dataset2” ( which I have already created from the console). Please replace ProjectID, Dataset1 and Dataset2 with suitable values.

Code:

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

query1 = """select table_name as table_name from 
(SELECT TABLE_NAME as table_name, TABLE_TYPE as table_type
FROM `ProjectID.dataset1.INFORMATION_SCHEMA.TABLES`) t1 where t1.table_type="EXTERNAL"
    """
# The above query returns table names of only external tables present in the dataset.

query_job = client.query(query1)
   
for row in query_job:
  
   # Row values can be accessed by field name or index.
       b = row[0]
       query2 = f"select * from `ProjectID.dataset1.{b}`"
       # In query2 I am doing a select * from the tables whose names are returned by query1
       
       destination = f"ProjectID.dataset2.{b}"
       # Creating the table in another dataset with the same name as was in the first dataset.
       job_config = bigquery.QueryJobConfig(destination=destination)
       query_job2 = client.query(query2, job_config=job_config)
       query_job2.result()

After I run the query I get the external tables copied to Dataset2 as native tables :