Uploading a database file | C2C Community
Solved

Uploading a database file


Userlevel 2
Badge +2

Started a new project. Added a MySQL instance. Now, I want to work on Chinook database as part of learning.

I do not think Import Option will be the right option as it asks for SQL/CSV file to import. In other words, this appears to be the place to import data that comes with a database. But the structure of data is yet not uploaded. 

 

Perhaps need to upload chinook.db file.

 

Guidance on how to start working with Chinook database appreciated. I have worked on Chinook database after downloading SQLite on my PC. Looking forward to replicating the same on Google Cloud.

icon

Best answer by malamin 3 August 2022, 17:27

View original

12 replies

Userlevel 6
Badge +8

Hello @DigitalSplendid ,

I appreciate you asking this. You were mistaken regarding the import function in Google Cloud. There is a file format choice, as you will discover if you look carefully at the import UI.


Which type of file format you wish to import can be chosen from this file format option. SQL files and CSV files are the two options.


Both file formats are quite accessible when obtaining data from any type of database system.


What to do right away is:


Data should be exported from your Chinook database as a SQL or CSV file. Once you have a CSV or SQL file with the complete table structure, you may upload it to Google Cloud Storage.

 

I hope it will help to solve your case.

 

 

 

 

 

Userlevel 2
Badge +2

I am not sure if the CSV/SQL formats will also include the Chinook database structure. To my understanding (I may be wrong), CSV file (if not SQL) is meant to include data but not database structure. If that be the case, I am not uploading the Chinook database structure. 

To be specific, do I need to upload chinook.db alone, or chinook.db (which if I am not wrong includes both database structure and data) along with CSV/SQL file, or CSV/SQL file alone. Also if it is needed to upload chinook.db, how to do given there are only two options I could see CSV and SQL.

Userlevel 6
Badge +8

Hi @DigitalSplendid ,

Thank you. Please first upload the database as SQL or CSV then visualize it with any suitable platform in GCP like bigquery, and data prep. Then, your confusion will be clear.  

Userlevel 2
Badge +2

It appears the easiest way to Import is to click Import and then create folder, followed by uploading CSV/SQL file.

 

Thanks for the support.

Userlevel 6
Badge +8

Hello @DigitalSplendid ,

To upload a file, you must first create the bucket, your own folder, and so forth.
You must also configure proper permissions to access the bucket storage file.


Another thing to remember is that the bucket storage name should always be unique.

 

 

 

Userlevel 2
Badge +2

Bucket is I understand another name for folder and objects another name of sub-folders/files within the bucket.

 

Userlevel 6
Badge +8

Hi  @DigitalSplendid ,

I like your dedication.You've come to the right place according to your screenshot, If you chose the correct bucket name and folder, the system will recognise the file and the select button will be activated.


You may need to reload the import interface to see the chosen button is active.


Also, ensure that you have the necessary files in your bucket storage.

 

Also, Manually you can enter your file location into the file browser field like following format.

gs://bucket/images2/example.sql

gs://bucket/images2/example.csv

 

Extra credit: Also you can check the following screenshot to get idea:

Assuming that you have already created a storage bucket under your GCP project.

If you have not created a storage bucket.

Upload  example.sql  to your GCP bucket

Go to GCP console, on the SQL instance overview screen, click the  IMPORT  option

 

Choose the file to import data from

You may need to click  ">"  to navigate to the desired file.

Assuming that your  example.sql  file is in a bucket named  …………….

 



Then click the  Select  button.



 




 

 

Userlevel 2
Badge +2

Thanks for patiently explaining. I am however still facing trouble.

Here is once again the screenshots.

 

 

 

 

Help in troubleshooting appreciated. Perhaps need to set binary mode to 1. Where to find this option to set?

Userlevel 6
Badge +8

Hi @DigitalSplendid ,

Did you check the service account privilege and google cloud storage file access permission?

 

Also, You need to check both side permission cloud storage files access permission and Google cloud SQL instance permission.

 

 

 

Userlevel 2
Badge +2

Thanks for the guidance.

I checked Service Accounts in IAM & Admin.

 

 

I believe I have access to ChinookSQL given I am login through techcosec2021@gmail.com.

 

“Also, You need to check both side permission cloud storage files access permission and Google cloud SQL instance permission.”

I need to see. However, being the only admin/user of this Google Cloud account, by default, such permissions are not granted to the main user?

Userlevel 2
Badge +2

Here are the screenshots in cloud storage permissions. They are set by default apparently and I believe I am the owner and should not have permission restricted.

 

Userlevel 2
Badge +2

When it comes to SQL,I could not find ‘permission’ tab. I understand it is Users for here. 

Here is the screenshot:

Once again, is it not that root user should be the one who has ownership of this cloud account and hence after login successfully through the main email id (techcosec2021@gmail.com), there should not be any access restriction.

Reply