Big Data SQL Cloud Service

Enables flexible data management and big data processing. Google BigQuery is one of the best ways to effortlessly run fast, large-scale SQL queries on extremely huge datasets which can also be combined with Cloud Dataflow for near real-time analysis.

BigSQL is an open-source, relational database that uses Apache Hadoop (HDFS) for storage. It supports ANSI SQL-based query language and allows querying through JDBC/ODBC compatible drivers. BigSQL integrates well with Apache Hive, Pig, etc., thus allowing easy integration with other components in the Hadoop ecosystem.

Are you looking for a way to process large amounts of data

BigSQL is one of the best ways to effortlessly run fast, large-scale SQL queries on extremely huge datasets which can also be combined with Cloud Dataflow for near real-time analysis. BigSQL integrates well with Apache Hive, Pig, etc., thus allowing easy integration with other components in the Hadoop ecosystem. It supports ANSI SQL-based query language and allows querying through JDBC/ODBC compatible drivers. You won’t find another solution like it on the market today!

We know that running big unstructured data processing jobs can be challenging at times but we are here to help you out! With our flexible private cloud computing platform, you will have access to all your data no matter how much it grows or shrinks over time. Our team is always available if you need any assistance along the way so don’t hesitate to reach out! Click this ad right now and sign up for a free trial of Geolance today!

Create Instance Wizard

  1. From Compute Engine section click the "Cloud SQL" item

  2. Now under the "Quickstart" section click the "Start Cloud SQL Instance Wizard"

Big Data Tools Support in Google Cloud Platform

Google BigQuery supported Standard SQL, not legacy compatibility mode. It also supports joining multiple datasets and running queries on external data science sources.

  1. Enter your project ID in the "Project ID" textbox

  2. Select MySQL database engine by clicking the radio button next to it

  3 . Enter an instance name for your server in the Instance Name text box

  4. Enter a password for the root user in the Root Password field, confirm it by entering it again when prompted for confirmation

i) Use 'default' zone

ii) Specify network or subnetwork that you want to utilize

  5. Select a machine type for your server in the Machine Type drop-down list

  6. Specify a disk size for your server in the Disk Size drop-down list

iii) Choose several disks

  7. Finally, click the "Create" button to be taken to your new instance's details screen where you can view operational logs and manage firewall rules for this project as necessary

BigQuery Overview

Google BigQuery is an enterprise data warehouse that allows querying massive datasets using standard SQL queries. It supports ANSI SQL by design which essentially means that any tool or application that can connect to a standard relational database can also connect with BigQuery without requiring modifications. Not only does it work with existing tools seamlessly, but it also comes with a rich API and command-line tool to make embrace big data insertion and queries easy for developers.

BigQuery scales up from single servers to thousands of machines without compromising response times. It is capable of processing hundreds of terabytes of structured data as well as enormous datasets containing trillions of rows. BigQuery can be used for a variety of tasks such as ETL (Extract, Transform, and Load), big data analytics, application development among others.

i) Access the Google Developers Console at https://console.developers.google.com/project

  1 i) In the "Project" dropdown select your project

  2 i) In the left menu select API's

  3 i) Click the BigQuery option

j) Click "Enable"

  1 j) Under "APIs & auth" section click on "Credentials"

k) Click the blue Create New Key button

  1 k) Type a name for this new service account in the Service Account Name field

  2 k ) Select 'Compute Engine default service account' radio button

  3 k ) Ensure 'Furnish a new private key' is checked and click Continue

  4 k ) In the "Key type" drop-down list select P12        

  5 k ) Under the P12 keys section click on Create p12 key to generate a new private key for this service account

  6 k ) Download the p12 file and save it as a secure location on your machine

l) Click the blue Create credentials button

m) In the list of credentials click "OAuth consent screen" located under the "APIs & auth" section

  1 m) In the 'Product name shown to users' field type a name for this service account e.g. BigQuery Service Account

  2 m) In the 'Email address' field type an email address for this service account e.g. bigquery-service@<your_project_id>.iam.gserviceaccount.com    

Setting up BigQuery Client Libraries

There are two libraries available that make it easy to interact with BigQuery from your application, command line, or development of cloud environment.

a) Apache HTTP Client Library This library is available for Java and PHP applications. b) Google API Discovery Service This service allows developers to easily implement their application logic without writing any additional code by accessing BigQuery through the Google APIs discovery service.     

1 i) Navigate to this page on apache.org at https://github.com/google/api-client-java

2 ii) Under "Download" click on the "Pre-releases" link located next to the newest version number     

3 iii) Download the .zip file corresponding to your machine type (e.g., API-client-java-6.5.1-beta API-client-java-6.5.0-alpha )

4 iv) Unzip the file and navigate to the "dist" folder

5 v) Double click on either API-endpoints-snapshot-[YO]MMDDHHMMSS.jar or API endpoints snapshots zip as appropriate for your machine type

i) Navigate to this page on apache.org at https://github.com/google/discovery-v1/  

ii) Under "Downloads" click on the latest version number

iii) Download the .zip file corresponding to your machine type (e.g., discovery -v1-snapshot- [ YO ] MM aDDHHMMSS. zip )

iv) Unzip the file and navigate to the "dist" folder

    i) Open a command prompt/terminal session in which you have to write access to the directory where you unzipped discovery-v1

     ii) Run java -jar discovery-v1-[YOUR_MACHINE_TYPE]-SNAPSHOT.jar --listen http://localhost:8800/apis/

    iii) In your browser navigate to this URL: http://localhost:8800/#!/apis/           (where #! is replaced by whatever port number was returned when you executed java -jar discovery-v1-[YOUR_MACHINE_TYPE]-SNAPSHOT.jar --listen http://localhost:8800/apis/)

Using BigQuery Client Libraries     

 a) Apache HTTP Client Library (for Java and PHP applications) If you are using a programming language that supports the Apache HTTP client library then we recommend that you use it to access BigQuery since this is the officially supported library. You can install the libraries into your development environment by following these steps

i) Follow the download link for this library at https://github.com/google/api-client-java

ii) Under "Download" click on the "Pre-releases" link located next to the newest version number

iii) Download the.zip file corresponding to your machine type (e.g., API-client-java-6.5.1-beta API-client-java-6.5.0-alpha )

iv) Unzip the file and navigate to the "dist" folder

v) Double click on either API-endpoints-[YOUR_MACHINE_TYPE].jar or API endpoints zip as appropriate for your machine type

    a) Google API Discovery Service (for custom application logic) If you are writing a new application or running an existing application that uses BigQuery then the recommendation is for you to use the Google API Discovery Service to access BigQuery.

Synchronize query server with Hive

The query server supports a Hive metastore interface. This support is intended to provide a migration path for existing applications that currently use Hive as the system of record for query execution. Metastore support is available in BigQuery, but it is an experimental feature and should be considered unstable until we announce otherwise.

Running Queries To run a query simply construct a QueryJobConfig object with the appropriate parameters, set up your service account credentials, and send your Query Job to the job queue using en queue. The following sample code demonstrates how this might work:

    // Create a configuration object .

     // Update the log level.

     // Initialize service configuration.

      .setJobName("My Query")

   .setQuery("SELECT corpus, COUNT(*) as count " + "FROM [publicdata:samples.shakespeare] " + "WHERE YEAR(corpus_date) = 1607 GROUP BY corpus;")

On success, you will receive an instance of Job that contains information about the status of your query job, including its ID and progress. You can use this job ID to poll on the status of your query (e.g., how far it has progressed), or to cancel the job outright if necessary by calling en queue again. Once the job has been completed, you can access the results by calling get query results(jobID) which returns an instance of QueryResultRDD that represents the BigQuery table created for your query job.

By default, any BigQuery table that is created as a result of a query job will be automatically deleted when the job completes successfully (i.e., once the user has closed their browser or disconnected from ADB). If you wish to retain access to this table after your query completes then it is recommended that you explicitly set up permissions to allow your application to act as a Viewer on this table

Cloud SQL Query Processing

This section describes how to use the Cloud SQL Admin API to start a data load job.

Start aJob) throws IOException {

  // Create GoogleCredential from GoogleCredentials object.

   .setUser(user);

  CloudStorageAccount storageAccount = new CloudStorageAccount(credentials, null);

    JobKey key = new JobKey();

  // Set up input and output locations for jobs. InputLocation inputLocation = new SqlInputLocation(queryInputPath);

     Stream sqlStreams = client.getStreamFactory().createReadStream();

   OutputLocation outputLocation = new SqlOutputLocation(writeOutputPath + "/jobs/"+jobID);

   .addJobOutputLocation(outputLocation);

    // Run query and obtain results.

     sqlStreams.setBatchLimit(Integer.MAX_VALUE);

   StreamExecutionResult execResult = client.query(sqlQuery, sqlStatementHandler); The following sample code demonstrates how to run a SQL SELECT statement against your Cloud SQL instance:

// Read the results into an RDD for processing.

 .addDecoder(TextLine.<String>decode().toDF()).get();

The various versions of Java in Google App Engine are not compatible with the JDBC drivers that are provided by Oracle for download on their website, which means you cannot use standard tools like My SQL Workbench or Toad to connect to Cloud SQL and administer your databases.

Connecting to Query Server with JDBC

Using ADB's command-line tools, you can connect to a Cloud SQL database using the standard Java ODBC drivers for MySQL, as long as the appropriate jar files are placed in your local classpath.

To use the connection string from an application written in any JVM language, simply change all instances of /cloudsql/main.cloudsql.google.com & & to reflect the name of your read replica (e.g., us-central1-f or Europe-west1-b), and prefix it with /jdbc:mysql:///. Replace my_password with the password you've configured for your instance.

mysql --host= /jdbc:mysql:///my_password@/cloudsql/ main .cloudsql.google.com The following sample code demonstrates how to connect to a Cloud SQL instance using JDBC from any JVM-based language, including Java, Scala, Clojure, and JRuby:

 try {

  Class.forName(driver);

  con = DriverManager.getConnection(" jdbc : mysql : ///"+user+"@"+connectionURL+"/"+databaseName);

   } catch (Exception ex) {

   logger .error("SQL State:" + ex .getSQLState() , ex);

    } finally {

   try {

     con.close();

Build and Deploy Your Application Once you've gone through the steps above to configure, create and make your App Engine app connect to Cloud SQL, the next step is to build and deploy your new application on Google's cloud platform. The App Engine development app provides a simple interface for deploying your applications onto Google's cloud infrastructure without requiring any specific knowledge of how Google manages its hardware or software stacks underneath.

Create external tables in a Cloud SQL for MySQL database and then access them from an App Engine Flexible environment application.

The information below describes how to create, access, and delete external tables in a Cloud SQL for MySQL Second Generation instance using the Google APIs client library for PHP. To work with this client library you need to have Composer installed on your system.

A Database instance is composed of one or more databases that contain their tables, indexes, users, permissions, and so forth. The DB instance can be used to manage these objects which makes it easy to transfer data between instances.

  $key = '@user_secrets["cloudsql"]["credentials"]';

  $conn_id = getenv("CLOUDSQL_CONNECTION_ID");

  $client = new SQLClient($conn_id);

               $username) . '@' . $host;

               $password) . '@' . $host;

   ->setDefaultsFromEnv() ->query($sql, function (\PDOStatement $stmt) {       if ($stmt === false) {                    throw new Exception(sprintf('Could not execute "%s" for user "%s".', $sql, implode(', ', array_keys($stmt->errorInfo())))) ;    } elseif (empty($r)) {                                 throw new Exception('No results from "%s" for user "%s".', $sql, implode(', ', array_keys($stmt->errorInfo()))));    } elseif (is_object($r) && !empty($r->fetchAll())) {      return $r;        } });

               // Create a table that stores the dataset.

       sql = 'CREATE TABLE IF NOT EXISTS estimated_attributes ('

       sql .= " name VARCHAR(32),"

       sql .= " value FLOAT)"';

         r = $client->query($sql);

               if ($r === false) {

                                   return new \Exception('Could not create table for user "%s".', implode(', ', array_keys($stmt->errorInfo()))));

           } elseif (is_object($r) && !empty($r->insertId)) {

       sql = 'INSERT INTO estimated_attributes ('

       sql .= " name,"

       sql .= " value)"';

               $client->query($sql);

""" DataLoader `s core entrance point is a method named load, which takes an input dataset and iterates over its elements in parallel. It calls the loader implementation once for each element to produce a result. A result is some data about the input element. It can be binary data, or it can contain metadata. Each loader gets its method chain for chaining the load calls together so that each loader has access to all of the previous results in the chain.

""" DataLoader uses ^x notation for documenting examples that are not part of normal Python code syntax.

# This "loader" just returns elements as they are sent to its

class ExampleLoader(DataLoader):

     yield 0; # yields one value per element passed into `load`. The values might be different than yours!

     yield 1; # these will both yield serialized float32s (not necessarily 32-bit floats)

Integrating Cloud SQL with Autonomous Databases

Aurora storage and SQL share the same namespace. While most Aurora backups will work with standard SQL, some types of backup operations require special processing. These special cases are documented below:

Customers may need to transfer data between a production instance and a local development or testing environment. You can use Google Cloud Storage for this purpose. If you do not want to use multiple buckets (recommended), create one bucket in each project/instance that needs access to the transferred data. By using different buckets, you can easily revoke read-only access from your local environments while keeping write access enabled for your production instance only if necessary. Here is an example of how to upload new files into a specific bucket:

  $key = '@user_secrets["gs"]["credentials"]';

  $bucket = 'my-bucket-name';

               $client = new Google_Client();

         if (empty($r)) {                                 throw new Exception('No results from "gsutil ls $bucket".');   } elseif (is_object($r) && !empty($r->nextPageToken)) {      echo "Retrying upload in 5 seconds...";     sleep(5); // wait to retry.      } else {      echo "Upload successful!";    }

In addition, you can download data stored from your bucket using the gsutil tool. The following command will download all of the files in a bucket to your local machine:

               $key = '@user_secrets["gs"]["credentials"]';

Do not forget Google Data APIs require billing to be enabled for your project. You can enable billing by going to the Billing section on the Google Cloud Platform Console.

Cloud Bigtable is an "SQL compatible relational database oracle big data service that provides easy-to-use, strongly consistent, fully managed tables." It has several use cases with broad applicability depending on the specific data model used. Cloud Bigtable is capable of efficient storage and retrieval of large table datasets using the HBase API. The HBase API provides an efficient interface for row-oriented storage. This means that Cloud Bigtable can handle high write volumes at low latencies when ingesting new data into a table.     

Specifying Hive Databases to Synchronize with Query Server

The Query Server can automatically synchronize certain Hive databases with Cloud Bigtable. For example, the following command configures the Query Server to automatically synchronize all or part of a database named "hive":

   --load-config=my_hive.json

See also:

Loading Data into Cloud Bigtable from Cloud Firestore

Cloud Firestore stores data as documents organized within collections and fields spanning those documents. Loading data from Cloud Firestore into Cloud Bigtable means defining those documents and fields as you would in a typical SQL schema. For example, consider a document collection containing two types of records:   {      "firstName": 'John',      "age": 43,      }     {      "firstName": 'Jane',      "age": 45,      }

In this example, the following table schema may be produced: CREATE TABLE IF NOT EXISTS person ( id bigint PRIMARY KEY, first_name string NOT NULL, age int NOT NULL );

CREATE TABLE IF NOT EXISTS person_contact (    id bigint PRIMARY KEY,        contact_id bigint REFERENCES person(id),        first_name string NOT NULL,        age int NOT NULL ) DISTRIBUTE BY HASH(id);

SELECT COUNT(*) FROM people;

 SELECT * FROM people WHERE first_name='John' AND age=43;  

SELECT * FROM people_contact WHERE contact_id=5 OR contact_id=6;

In this example, the "people" table represents a Cloud Bigtable Cloud Datastore table containing data from Cloud Firestore. This example also shows how to load the "people_contact" table into Cloud Bigtable with an SQL query directly on that table. For more information about automatic synchronization of Hive databases for your Query Server instance, see Querying Data in a Sync Database.

Much like uploading files into a bucket using gsutil, you can download files by specifying the file name(s). When downloading multiple files, these filenames must be separated by commas:        $key = '@user_secrets["gs"]["credentials"]';        $destination = 'path/to/local/folder';      

About Cloud SQL Query Server Cloud

SQL Query Server, an instance of Google Cloud SQL, provides a MySQL instance to which you can connect using gcloud command-line tools. You can also use the traditional MySQL CLI or any other compatible connectors that are available for your platform. To learn more about connecting to Cloud SQL Query Server with the MySQL CLI, see Connecting with MySQL.

For more information about loading data into Cloud Bigtable directly from Cloud Firestore, see Loading Data into Cloud Bigtable from Cloud Firestore.

Scaling Instances The number of instances you need depends on your traffic patterns and how quickly you want requests to complete. For example, one t2.medium instance is enough for our largest web property ( www.googleapis.com ) to handle a couple of thousand queries per second.

Cloud SQL Overview

Cloud SQL provides fully managed MySQL databases, making it easier for you to create, configure, manage, and scale your relational Postgres or MySQL databases in the cloud. You can use Cloud SQL as a highly available data warehouse with no need for on-premises software, such as Cloudera Impala (incubating).

There are three possible ways to deploy this Architecture. You can deploy multiple instances of Cloud Bigtable across geographically dispersed locations, scaling up the number of regions as your traffic grows. Alternatively, you can deploy one or more local instances of Cloud Bigtable per location and scale workloads by deploying more instances of those apps that use these databases in big data projects and big data initiatives. Finally, you can also combine those two concepts by using a single instance of Cloud Bigtable for some types of data and a set of local instances for other types.

Components of a system

Generally, the components of a system follow the architectural layers concept. If you're familiar with three-tier architecture, you can imagine that your apps (the presentation layer) sit on top of Cloud SQL (the logic or service tier), which, in turn, runs on Compute Engine (the physical hardware).

At the highest level are Google APIs Client Library for C++ and cloud command-line tool. These provide access to all Google APIs using language-specific primitives such as classes and methods. The client library is also used by various editor plugins for languages like IntelliJ to make it easy for developers to integrate Google cloud services into their development workflow.

Similarly, Dataflow SDK provides data ingestion templates based on the Apache Beam model to simplify the ingestion of data into BigQuery. Google provides both the source-to-target mapping and the transformations.

Dataflow SDK can also be used to transform your Cloud Storage data, for example, to convert Avro files exported from Cloud Dataproc clusters to Parquet files for efficient storage in BigQuery or Cloud Composables enabled by Dataflow SDK transforms Sqoop/Avro/HCatalog sources directly into BigQuery tables without any ETL pipeline between them. Additionally, it can also be combined with PubSub streams for real-time aggregation & enrichment that simplifies the process required to collect logs or telemetry data from production services that are running at scale.

The following table lists some of Google's open-source libraries that are either new or have been recently released.

Google Cloud Datastore is a fully managed, schemaless, highly-scalable NoSQL database cloud service provider for all your web and mobile application data center storage needs. It features powerful 3rd generation distributed SQL queries and rich indexes with the ability to fetch records by any field sorted by another field. Furthermore, it offers synchronous replication across multiple data centers and provides strong consistency guarantees.