Google Associate Cloud Engineer – Exploring Database in Google Cloud Platform Part 2

7. Step 04 – Getting started with Cloud Spanner

Welcome back. In this app, let’s start playing with Cloud spanner. You can just search for cloud spanner and you should land up on this specific page where you can enable the API for cloud spanner API. So let’s go ahead and say enable cloud spanner API. Cloud spanner is a managed, mission critical, globally consistent and scalable relational database. It took about a minute for the API to be enabled and I’m ready to create my cloud spanner instance similar to Cloud SQL. Even in cloud spanner you need to create an instance before you’d be able to create data and databases. So let’s go ahead and say Create instance and let’s call this my first spanner instance. You can either go regional or multiregional.

As we talked earlier, cloud spanner is a global database and over here we can select a specific region as well. Let’s choose something which is nearer to me isha south one you can choose any region should not be making a huge difference, and you can configure how many nodes you’d want in your cloud spanner cluster. The default is one. You can start with one node, and as your needs increase, you can scale it up to multiple nodes as well. I can go here and say Create. So this would create our cloud spanner database. Once we have the instance ready, we can go ahead and create our database. Let’s go ahead and say Create database. I’ll say my first database and I’ll say Create.

So we have first created the instance, then we created the database. This is the same as cloud SQL where we first created an instance and inside cloud SQL we created a database. And once we have a database is where you can create your tables and populate your data. So the database is now created and we would be taken to the specific database right now. So this is the instance, inside the instance we have created a database and inside the data is where you can actually create your tables. So I can go here and say Create table. So you can write your DDL in here. So database Definition language for SQL statement in here. Let’s create a very simple table. So create table.

I’ll call this User and C should be small, so create table user. Let’s define the columns. I would want a user ID column. The column types in cloud spanner are a little different, so it’s int. If I want to create an ID field, typically I would use int 64, that’s a type. Let’s say it is not null to define a constraint, and I would add another username field as well, which is string. Let’s say I would want to have a specific number of characters, so 1024 characters, large user names, and I can mark the primary key, so I can say primary key is User ID. We are creating a very very simple table with two columns user ID and Username and a primary key is user ID and let’s put a semicolon in here and instead of braces I should have actually been using parentheses so let’s fix that as well.

So create table user within parenthesis the column definitions that looks good, let’s go ahead and say create so that will create the user table for us. You can see the scheme of it here user ID in 64 username string of you can go and look at the indexes right now there are no indexes. If you want we can actually create our indexes. You can go to create index and create any index that you would want to create. I don’t really want to worry about the indexes for now. Let’s do a cancel, let’s look at the data.

You can populate data in here so if you want to insert a row you can go in here and say insert and let’s say user ID is one and the user name is ranga and save so that’s your data population and if you want you can query as well. So you can go in here to query and you can type in the query in here.

Let’s type in some simple SQL, select star from user, let’s capital you user and let’s say run query and you can see the details which are coming back. As you can see it’s like any other database, you create an instance, you create a database, you create your tables and you can query them using your query. Once you use this cloud panel database for a little bit of time you’d be able to see more metrics around here. You can right now see the Cputilization metrics in here, you can also look at the monitoring tab for more information. You can look at a lot of other metrics in here and you can also look at query stats so you can look at which query, how many times is it executed, how much time it took to execute and all the details around that.

In addition to looking at the queries, you can also do imports and exports. If you click export you can actually create an export from your database to cloud storage. I’ll do a cancel in here. You can also import data from cloud storage as well so you can create import and export jobs using cloud dataflow. The other options you have are respect to backup and restore. You can go to your backups right now we don’t really have any backups present. If you don’t, you can go ahead and create a backup. When you’re creating a backup you can choose which database you want to back up, what is the name you’d want to give and you can also set an expiration date.

How long do you want to keep the backup for? The other operations that you can do in here once we have a few backups is to restore from the backup in this up we looked at cloud spanner, we saw that playing with cloud spanner is no different from playing with any relational database. Running cloud spanner database for a long time is very expensive. What I would recommend you do is go ahead and delete the database as well. So let’s go ahead and say delete instance and let’s type in my first cloud spanner instance and say delete the deletion of the cloud spanner instance would take a little while and I’ll see you in the next step.

8. Step 04a – Demo – Playing with Cloud Spanner

Come back in the step. Let’s switch to the NoSQL options. Let’s start with cloud datastore and Firestore. Datastore is a highly scalable, no sequel document database. It automatically scales and partitions data as it grows. It is recommended for up to few terabytes of data for bigger volumes. Big table is recommended. Data Store supports transaction indexes and it supports its own query language which is called GQL. It is very very similar to SQL. However, important thing to remember is that Datastore does not support, joins or aggregate. It does not support operations like Sum or Count. If your use cases need flexible schema with transactions, for example user profile or product catalogs, then you can go for Datastore.

The structure of Datastore is you create a Kind and inside the Kind you create entity and you can use namespaces to group your entities. With data store. You can only export data from Google cloud. You cannot do it from Cloud console. It’s important to remember that the export contains a metadata file and a folder with the data, so it has a description of the data and the folder that contains the data. Google is slowly replacing datastore with Firestore. Firestore is datastore plus plus. In addition to the features provided by Datastore, Firestore is optimized for multidevice access. It provides you with an offline mode and data synchronization across multiple devices mobile, IoT, et cetera. Firestore also provides you with a variety of client side libraries, so you can interact from web applications or iOS applications, Android applications easily.

With Firestore, you can run Firestore in two different modes in a datastore compatible mode and in a native mode. If you’re building a new application with Firestore, it is recommended to use the native mode. If you are migrating an application from Datastore to Firestore, it is recommended to use the Data Store mode in this quick step. We talked about Cloud Datastore and now now it is called Cloud Firestore. Cloud Datastore is recommended for use cases which need flexible schema with transactions. Firestore is an enhanced version of Datastore optimized for multidivised access things like offline mode and data synchronization. I’m sure you’re having a wonderful time and I’ll see you in the next step.

9. Step 05 – Getting started with Cloud Datastore and Cloud Firestore

Welcome back. In this up, let’s play with Cloud Firestore. You can easily get to Firestore. Just type in Firestore and you should be able to pick this link in here and you’d be taken out to Firestore. And the first thing that you need to choose whenever you want to use Firestore is whether you want to use the native mode or the data store mode. As we discussed earlier, Cloud Firestore is the next generation generation of cloud datastore. If you are creating new projects, then the recommendation is to go for native mode. If you have old data store projects that you are moving over to Firestore, then the recommendation is to go with data store mode.

What I would do now is I would actually choose the native mode. One important thing to remember is this mode that we are choosing in here will be permanent for this specific project. For the my first project that we have in here, we will always use Firestore in native mode. Once you make this choice, you’ll not be able to go back on it. If you want to create something in a data store mode, you need to create a new project. So really be cautious when you make this choice in the real world projects. For now, what I would do is I would choose the select native mode in here. The next thing I would want to choose is the location. You can either go for multiregion or regional. I’ll choose regional for enough. I’ll choose regional for enough.

Now I chose the location as HDR South one and I’ll say create database. So what we are doing in here is creating Cloud Firestore database in native mode. It took about five minutes to create the database and at the end of it a screen like this came up. Once you create the database, you are now ready to populate the data in here. How do we add data into Firestore? The way we add data into Firestore is by adding a collection. So I can say start collection. A collection is a set of one or more documents that contain data. What we are storing in here is documents and a collection is nothing but one or more documents. If you look at general way we store data, we’ll store it into rows. A table is nothing but a collection of rows. Similar to that, a collection is a set of one or more documents. So you can see a collection as something very, very similar to a table. Over here I would enter the name of the collection, which is to do so, I would want to store a list of todos. And over inside the todos table, we would want to create a row. We would want to create a document. So what we are creating in here is a document. I’ll take the ID which is suggested by the document itself. And then I would enter a description. I’m entering the specific to do description, I would say Learn GCP and I would add another field. Over here, let’s say the target date.

So target underscore date, the type of it is I would want to use timestamp and let’s choose the default timestamp it’s suggesting. And now I can go ahead and say save. So inside the Firestore we created a collection and inside the collection we have one document. Right now we want to add more documents. So I’ll say add document and I’ll enter a field name, the same field name as earlier description and I want to say Learn AWS and sure as well. And let’s add another field target underscore date. I’ll have it as a timestamp and the value and I would say save. If you want to add more documents, you can also do Save and add another.

That’s what I’ll do right now, save and add another. So it says the previous document was saved and the field names, types and values from the previous document have been copied into this one as well. So now I can actually change this get certified by GCP. So we have created total of three documents, right? If I save this, I’ll have total of three documents right now. So the first document, what does it contain? It contains. Learn GCP. The second one it contains Get certified by GCP. The third one. It contains Learn, AWS and Azure. One of the important things to remember about Data Store or Firestore is the fact that it is hierarchical. Inside this document, again, you can add a collection.

So we have a collection, inside the collection, we have set of documents and inside the document, again you can either add fields so you can add more data for this specific to do or you can start a collection as well. So I can say start collection and I can say activities. So inside a specific Todo document I’m saying I want to log all the activities that I am performing for that specific Todo. And over here I can add activities in here, so I can say a field name and I’ll say text and I’ll say bought a course. I can say save and add another to add one more started. The next thing I did was spend an hour on the course. Let’s say save enough. You can see that we are going and looking at the activities.

So in activities we have spent an hour on the course and the other one is bought a course. Now you can see that there is a hierarchy which is present in here. So we have created a to do’s collection and inside the Todo’s collection we have a number of documents and inside one of these documents we have actually created a little bit more of a hierarchy as well. If you pick up the learn areas and Azure one where we created the activities, you can actually click activities and you can see that this one again is a collection and inside that there are multiple documents. As you can see in here, Firestore allows you to create a hierarchical data structure so your collections contain document, document contain collections.

The next interesting thing is indexes. Indexes are automatically created on all your fields. However, you can go and create more composite indexes if you want to be able to query on two fields at the same time. In those kind of situations you’d need to create an index. For example, I can go ahead and create an index in here. I’ll say where are the fields that you would want to index? I’d want to index it to do and I would want to be able to search based on description and target date fields. So these are the two fields based on which I would typically be querying on. So I would want to be able to create an index based on that. I can go out here and say Create.

Now if you scroll down a little, you can get to choose the query scope. You can either query in the collection or in the collection group. For now I just say collection. And I’ll say create. So this would create an index and you’d be able to query efficiently based on description and target data as well. If you go over to single field in here, you’d be able to see automatic indexes that are created. As you can see in here, cloud Firestore automatically indexes every field you add, enabling more simple queries. By default, if you’re querying by just one column, then there is an automatic index which is created. I’ll go back to Data and let’s go back to Todos.

One of the other interesting options in here is that you can filter so I can go in where did we click filter? This is the one which I’m clicking right now. So you can click this in here and you can say I would want to search based on description and I would want to let’s start with adding no condition for first. And let’s say I would want to sort the results in descending order of the description. So you can see that this is the query code, right? So you can see that this is the way you can actually query. So dot Collection to do, order by description descending and let’s say Apply. So you can see that they are all order by description in a descending order.

If you want you can go and go ahead and filter them as well. So let’s say I only want this one Learn GCP. So I’ll copy this, I’ll copy the value, I’ll say cancel, I can go in here and I can filter on it. So I can say a condition equal to string and I can enter Learn GCP as the value. You can see that the query code is now Collection to do where description is equal to is equal to. Learn GCP I’ll say apply, and then you’d be able to see that only that specific row is filtered. Now, other than playing with data and indexes, you can also try and import and export data for Firestore. Most of the database services provide features to export data and import data from Google Cloud Storage. So Google Cloud Storage is what we are talking about in here.

It should be very, very easy to do exports or imports from here. If you want to export all the data which is present in here, you can just say export. You can say I would want to export the entire data, or you can just pick sweet collections and then you can choose which bucket. So you can say browse in here, and you can say which bucket I would want to upload, or you can specify which bucket I would want to export the data to. I don’t really want to do the export, so I’ll say cancel in here. Similar to that, you can also do an import. When you run import, you need to first choose the file name. So you need to click the browse. You need to choose the specific bucket, and from the bucket you can choose the specific file.

Once you choose the bucket, you will be able to select the file from which you want to import into the database. As you can see in here, playing with Firestore is very, very easy. It’s all about creating your collections. Inside collections, you have a number of documents. The entire structure is very hierarchical. So even inside the document you can have a collection with how to query data. We saw that default indexes are created for all the columns. If you want to query based on multiple columns, then we can create specific indexes for that. And we also saw how to import and export data. As is the case with most databases, you can easily export and import from cloud storage. I’m sure you’re having a wonderful time and I’ll see you in the next.

• Category: Uncategorized