Uploading to Imvu I Havent Never Use Skype
Upload and Import SQL file from Google Cloud Storage to CloudSQL using NodeJS
Hi, folks. This month I get an interesting project that require me to learn about Google Cloud or more specifically BigQuery, Google Deject Storage (GCS) and CloudSQL. At first I was and so excited having a hazard to play around with Google Deject Platform since I'chiliad likewise lazy to get myself a credit card and register for the Google Cloud Platform trial. Simply, my excitement doesn't last long because the documentation for using NodeJS to access google storage and CloudSQL is too unpractical for me. I mean when people say documentation, they usually means stride-by-step guide that targeted to someone that never employ the production so they could understand and use the product, right? But I suppose google accept their own definition of documentation. Or maybe I'm not smart plenty to empathize the documentation. Well, honestly I don't think the later is possible :).
For this project, I spend hours on stackoverflow and watch bunch of youtube videos just to sympathise how to make Google Deject Platform works on my NodeJS. It's so frustrating and time consuming therefore I don't want anyone else to go through the aforementioned pain and suffering that I experienced. Then, in this commodity I will explain step-by-stride on how you lot tin can apply NodeJS to upload an SQL file to GCS and then import information technology to CloudSQL. I volition include as much screenshot as possible for you visual learner out at that place so I hope from this article you tin learn:
- How to gear up up Google Deject Storage and CloudSQL
- How to get Google Cloud Storage and CloudSQL credential JSON file
- How to admission CloudSQL using Google Cloud Beat and Sequel Pro
- How to upload local file to GCS using NodeJS
- How to import SQL file from GCS to CloudSQL using NodeJS
It will a long article but I volition provide a subheading then you tin can jump to specific part of this article that yous need. Or if you are here just for the juicy code, you lot can check the Github repository here.
Prepare Google Storage
First, let's encounter how GCP dashboard wait like at this url and you will see something like this:
On the left bar you can meet all the services that google provides y'all. For now let's choose storage and click "Browser". By the way, you could pivot the storage button, and so it volition be displayed on top the next time you open the dashboard. This feature is definitely a big assistance for someone like me who is also lazy to scroll down and somehow all the same can't notice the menu I'thousand looking for.
After that, we will need to create new bucket for our google storage by clicking "CREATE Bucket" on the interface as illustrated below:
So, only fill the form depends on what you need and finally click create. At this step, you might need to wait for several minutes for GCP to create your saucepan. In case you lot doesn't know what bucket is, you lot can imagine information technology like a your local storage sectionalization and the process that nosotros're doing at present is just creating new partition in our cloud storage.
Afterwards you create a bucket, you can upload and download to google storage using the console, but you volition not be able to do that using NodeJS all the same. To allow NodeJS to admission Google Storage we need to enable the GCS API. To do that, get to the left bar, hover to "APIs & Services" and click "Dashboard"
Now, click "ENABLE APIS AND SERVICES" and search for "Google Cloud Storage". Subsequently that you can only click "ENABLE" to enable GCS API. Here is the enabled GCS API looks like in the dashboard:
Prepare CloudSQL
Afterwards we create our GCS bucket, now we need to prepare our CloudSQL services. Let's go to the left bar once more and click "SQL" to open CloudSQL dashboard.
Now, we need to create new instance past clicking "CREATE INSTANCE" on the top of dashboard like what illustrated in the picture below:
Then nosotros demand to choose the type of database that nosotros prefer. For this article let'southward choose MySQL and and then hitting "Side by side".
After that, we also demand to choose the type of MySQL instance that nosotros demand. Let's choose the second generation since google is kindly recommend it to us.
Now, we need to fill in some grade to create our MySQL instance. Brand sure your instance ID is unique and cull location closest to your current location.
Finally, click "Create" and as usual you might demand to expect for several minutes for google to create your MySQL instance. Just like the GCS, we also need to enable the API for CloudSQL to permit access for NodeJS. Go to "APIs & Services" and then search for "Google Deject SQL" and click "ENABLE". This picture below is what enable Google Cloud SQL API looks like:
Create CloudSQL Database
Information technology is actually a simple things to create CloudSQL database. Yous only need to connect to the CloudSQL and use your standard query SQL to create new database, new table etc. But some people might have difficulty to connect with CloudSQL database. Well, at to the lowest degree for me I froze for 2 whole minutes before I effigy out there is a push button to connect to it. There are ii methods that I have tried to connect which are using Google Deject Trounce and by giving dominance to my network. At that place are other method like using SSL to connect just I will non cover that in this article since I haven't tried it yet.
To connect using Google Cloud Shell you but demand to open up your CloudSQL dashboard, click on the Instance ID that you just create and good luck finding "Connect using cloud beat". Just kidding, it's not that difficult just the first time I open this page, somehow I but forgot that you tin can scroll downwardly to see more option on this folio. LOL. Anyway, after you click that button, it will open google cloud beat and yous demand to press Enter to run the command there. Every bit usual when you printing enter, you might need to look for several minutes for google to whitelist your IP. After that you lot tin can type your MySQL password to login into your MySQL database.
If in the get-go method, the platform will automatically whitelist your IP temporarily, in the second method we volition whitelist our network IP manually. By using this method yous volition exist able to access CloudSQL using SQL application such as Sequel Pro. To qualify your network, go to authorization tab on your CloudSQL dashboard and click "Add network" as follows:
At present yous volition be able to access CloudSQL using your network IP. To connect using Sequel Pro or whatever other app yous just need to use the IP address that you can find on overview tab, user proper noun (root) and the password you lot prepare for your CloudSQL.
Finally afterwards you manage to connect to CloudSQL, let's create a new table every bit follows:
Upload SQL file to Google Deject Storage
For this article, we volition endeavor to insert several row data to a table using this following SQL query:
To upload sql file or any file to GCS using NodeJS we need to install node package chosen @google-cloud/storage by using this following control:
npm install @google-cloud/storage --save Before we beginning to make a lawmaking, we need to get google storage credential file from GCP dashboard. This credential is the one that will let our NodeJS script to access our GCS. To go the credential file go to the left bar carte du jour and hover over "APIs & Services" but now instead of clicking "Dashboard" we click "Credentials".
Now later we see the credential dashboard, click on "Create credentials" and choose "Service business relationship key".
Subsequently that choose the service account that you want to employ and if you accept no service account just create one. Finally click "Create" to download the credential file. For this example we will utilize the JSON format credential.
Now let's get our hand muddied with code and use the post-obit code to upload the SQL file to GCS:
In the in a higher place code, "storage-credential.json" is the JSON credential file that nosotros get from google cloud dashboard previously. You might notice that in this code we likewise make our sql file public. To import SQL file to CloudSQL from GCS using NodeJS, nosotros demand to let NodeJS to admission the uploaded SQL file and that's why in this lawmaking we also make our file public. This picture below is showing how a public file looks like in GCS dashboard. Discover that in that location is check mark on the right column for a public file.
Import SQL file from GCS to CloudSQL
To import SQL file from GCS to CloudSQL, we need to install some other node bundle called googleapis. Utilize the following command to install googleapis node package:
npm install googleapis --save After that, we need to authorize NodeJS to access CloudSQL. Unfortunately, we tin not employ the previous credential or at to the lowest degree I can't find any reference on using the previous credential to admission CloudSQL from NodeJS. Therefore nosotros will use another manner to get credential which is by using Google Deject SDK. Behave with me, because this will be kind of inefficient method since we need to install SDK but to get a credential. But at to the lowest degree this method works and that's the most of import thing for people similar us, right? LOL.
Once you install Google Cloud SDK, run the following control to go the credential file:
gcloud auth application-default login This command will open a browser that ask you lot to login to your google account that continued to the Google Deject Platform. After that information technology volition generate a default application credential file for Google Cloud Platform in your local machine. You tin can alter the default location for the credential file by defining the new location at environs variable called GOOGLE_APPLICATION_CREDENTIALS, only allow'southward non make this article more complex that it already has and just use the default location.
Now we're ready to become back to our code. Create new file and blazon the post-obit code or honestly I'chiliad sure you merely gonna copy paste it anyway.
In this lawmaking nosotros use default application credential to get the authorization to admission CloudSQL and so utilize the authorization on sqlAdmin module that will import the SQL file from Google Storage to CloudSQL. By the manner, if you want to try the import API without implement it on NodeJS, yous can effort it on the documentation that Google provides here.
To run all the code all together from uploading to importing the SQL file, you tin utilise the following code:
Finally yous can see the result past using google cloud shell or Sequel Pro every bit follows:
That's all for today, if you have further question or the Github repository doesn't work for you, please feel free to contact me at himang@skyshi.io or if y'all prefer skype, contact me at alive:himang27s. Encounter y'all on the next article.
Source: https://medium.com/skyshidigital/upload-and-import-sql-file-from-google-cloud-storage-to-cloudsql-using-nodejs-e8041c6f5966
0 Response to "Uploading to Imvu I Havent Never Use Skype"
Post a Comment