Hi, I'm Hernan Liendo, one of the 13 Google Developers Experts Of Cloud Platform, I've been
a university professor for 15 years. I'm also the Co-founder of Zupcat. As I've been
telling you in the presentations, massive games are used by billon
of users. And tough is a game, we require to have a safety similar to a banking
application, because the games generate economic transactions and must be safe.
One of the things we did to control this was to generate audit records
of the events that the user games take. You can imagine that if we have
million users in some tables and those users, in their various sessions, generate
thousands of events, then we are beginning to have tables that have billions
of registers. In this presentation I will tell you how we can work with that amount
of data. BigQuery is an ideal solution to work with tables that have a big
amount of data, but its query does not occur so frequently. It is ideal for our
audit tables. BigQuery provides a interface similar to a SQL data
base, where we can do Select queries with known commands.
But it does something that SQL doesn't do. That is to process huge amount of information
very fast. This does it because when we execute a query, it instance multiple servers
in parallel to make it along several Terabytes of information in just seconds. A fundamental
aspect of BigQuery is its scheme price and this is the reason that we decided to use it.
In this scheme we're are not charged when we incorporate data into the database
or when we take them. The network cost is 0. About the storage, the cost is 10
times lower to the one we have in Cloud Datastore that is already low,which makes
the storage in BigQuery really low. What BigQuery charges is the amount of
bytes that are read when making a query, as in our case it is a
auditory case, we have a big flow of inserts of data because of the events that the
users produce, and that we rearly have to consult that data to
find a strange behavior, the pricing scheme is great, we only pay
when we do a query to the data base. Now, I'll show you, step by step,
the necessary code to connect to a data store of BigQuery to create structures,
to insert registers, and to consult them from a Java program. The first step
to use Query, is to activate the Billing system un our App Engine Application.
This is required because when we do queries to the data base we're incurring
into expenses. The next step is to give editing persmission to a special admin account
that already exists in our application, but we need to activate it. The next thing
we need to do is to connect to the data base, and this is quite different if we want to
do so from a hosted application in App Engine, or if we want to do it from a remote
application like the one we're running here en mi machine. In the first case, where we
connect from App Engine, we must follow these instructions
that I'll show you: first we can implement a type class to make sure
there's just one connection factory in our application and we must include the next
code. These options over here obtains the credentials of a administrator
account that runs in App Engine has. Is the one that we configured
before. This let us obtain the name of the application. And with these lines
we obtain an object that let us connect to BigQuery from an App Engine
server. If we need to connect from a client, the steps are
totalyy different. We have to implement this class. a client factory
from the side of the client, that when we run it for the first time, we'll have to indicate
the number of the project in which our BigQuery base is being used. We obtain the number
of the project in the overview field that's here, and we also have to
be able to obtain the name of the app. We'll get this in the same page in this
part. With this information we execute this code
that for the first time it will print in
the screen this message and it will give us a URL. We must enter this URL
with an authenticated account from Gmail that has permission in our BigQuery application,
and we have to give it the necessary permission, accept the permissions that we find when we go to the URL
that prints. Then, we must paste that security code in the application and finally
we'll obtain a connection to BigQuery. As we saw before, our BigQuery class
Client factory or our BigQuery server factory, give as a BigQuery instance
client. This class is already independent from where we're runing our queries, and
it let us execute directives to BigQuery.
With this method as an example we
can create a table. We must put the data set name that is kind of a table
grouping, the name of the table and a string to string map that will have
each field that we want to create and its type of data. This make a remote
requirement to BigQuery configured with the data that we gave to it. And finally it builds
the table with this code line. The next code is necessary to able to insert
registers to a BigQuery connection. This code is also in the BigQuery class client.
that we talked about before, and insert rows expects that we put
the name of the data set, the name of the table where we'll insert those registers and a
group of objects of string map objects type, where each of those registers is an entity,
is an instance of the map that has many values. In the key we'll obtain the names
of the fields and in the object we'll get the value of the fields that we want to insert.
We can do multiple inserts in a requirement through a list. This
code will create the necessary codes to make that remote call and finally
it will execute that insert all command that will insert in the data base.
It's frequent that an insertion requirement to the data base produces error. Because of this
we envolve it in this block that will try to execute this requirement 5 times in case
it fails. It wil also wait a second between each one of these executions. Lastly I want to
show you the necessary code to execute a query. A query that brings,
that gather tables, and that gives us a result. To do so we must follow this method
the execute query and give it a string, this string is one that supports BigQuery
that will also give back a map which key is the name of the field and which value is
the result. And as the result are a lot usually, that will be enveloped in a
list. This code basically prepares the Query, that we have
here and it will start to paginate the results, that can be many, until
it has no more results. These same queries can be executed in the BigQuery console
visually, so we can test it here. For example this one that I have here
that brings me all the auditory fields of a user that has this ID, ordered by
occurrence time of the event in a descendent way. Now I will show you
how we do a query in the BigQuery web interface. As you can see here I must access
to bigquery cloud.google.com and I'll be able to to load my queries. I'll bring an
old query. Notice is identical to SQL. In this case I'm bringing all the
registers of my auditory table where the user is the one that is here, ordered by
its occurrence date in a descendent way. When I execute the query I can see how long is
taking to obtain those dates. And next this will tell me the amount
of bytes that had to process to do this query. Remember that this relates to
the consult price, and down here I can see the results. This same query
can be executed with Java code, like I showed you in the previous code. BigQuery
is a great solution that is perfect for storage and massive events of users
analysis. In this presentations I was able to tell you some keys to operate heats. We also
talked about persistence strategies of massive data. We also talked
about Simple-datastore, a framework that we use a lot and that we were able to release as an Open Source project.
Finally I gave you many BigQuery details. I really hope that this presentations
are useful! I hope to see you soon!