Today, we are excited to announce that Google Cloud Data Catalog will be unified with Dataplex into a single user interface. With this unification, customers have a unified experience to search and discover their data, enrich it with relevant business information, organize it by logical data domains, and centrally govern and monitor their distributed data with built-in data intelligence and automation capabilities. Customers now have access to an integrated metadata platform that connects technical and operational metadata with business metadata, and then uses this augmented and active metadata to drive intelligent Data management and governance got easier the unification of Dataplex and Data Catalog

The enterprise data landscape is becoming increasingly diverse and distributed with data across multiple storage systems, each having its own way of handling metadata, security, and governance. This creates a tremendous amount of operational complexity, and thus, generates strong market demand for a metadata platform that can power consistent operations across distributed data.

Dataple provides a data fabric to automate data management, governance, discovery, and exploration across distributed data at scale. With Dataplex, enterprises can easily arrange their data into data domains, delegate ownership, usage, and sharing of data to data owners who have the right business context, while still maintaining a single pane of glass to consistently monitor and govern data across various data domains in their organization.

Prior to this unification, data owners, stewards and governors had to use two different interfaces – Dataplex to organize, manage, and govern their data, and Data Catalog to discover, understand, and enrich their data. Now with this unification, we are creating a single coherent user experience where customers can now automatically discover and catalog all the data they own, understand data lineage, check for data quality, augment with business knowledge, organize data into domains, and then use that combined metadata to power data management. Together we provide an integrated experience that serves the full spectrum of data governance needs in an organization, enabling data management at scale.

“With Data Catalog now being part of Dataplex, we get a unified, simplified, and streamlined experience to effectively discover and govern our data, which enables team productivity and analytics agility for our organization. We can now use a single experience to search and discover data with relevant business context, organize and govern this data based on business domains, and enable access to trusted data for analytics and data science – all within the same platform.” said Elton Martins, Senior Director of Data Engineering at Loblaw Companies Limited.

Getting started

Existing Data Catalog and Dataplex customers and new customers can now start using Dataplex for metadata discovery, management and governance. Please note that while the user experience interface is unified via this release, all existing APIs and feature functionalities of both products will continue to work as before. To learn more, please refer to technical documentations or contact the Google Cloud sales team.

Enterprise database workloads are the backbone of many of your applications and ecosystems. Also, guaranteed availability is critical when choosing a cloud provider.

Many enterprises built their mission-critical applications on Microsoft SQL Server 2008, and it’s common still to run into older versions of SQL Server as you’re working toward modernizing your on-prem environments.

According to Business insider, 60% of Microsoft users still use SQL Server 2008, which reached its end of life in July 2019. This provides the opportunity for many of you to find a place to host your SQL Server 2008 instances on newer technology with less operational burden. 

We’re announcing that Cloud SQL for SQL Server is generally available globally. This means that Cloud SQL now helps you keep your SQL Server workloads running by providing a 99.95% uptime service-level agreement (SLA), which is consistent with the other Cloud SQL database engines.

Cloud SQL for SQL Server is fully managed and compatible with SQL Server 2017. Now you can migrate your critical production SQL Server workloads to Google Cloud and rely on the service’s stability and reliability. 

We hear from enterprise companies how important the ability to migrate to Cloud SQL for SQL Server is to their larger goals of infrastructure modernization and a multi-cloud strategy. On-premises applications like HR, finance, and payroll often depend on these legacy databases to keep running.

Customers often cite the challenge of wanting to maintain compatibility with these existing systems and datasets, while also streamlining deployments and scale-out at a fraction of the overhead. Migrating these instances to Cloud SQL for SQL Server can save costs and maintenance time and improve efficiency and speed. 

Getting started migrating SQL Server 2008

The migration for Microsoft SQL Server 2008 to Cloud SQL for SQL Server can be achieved in a simple five steps. For details, check out the full migration guide: SQL Server 2008 R2 server to Cloud SQL for SQL Server. 

1. Create a Cloud SQL for SQL Server instance

gcloud beta sql instances create target  \
    --database-version=SQLSERVER_2017_ENTERPRISE \
    --cpu=2 \
    --memory=5GB \
    --root-password=sqlserver12@ \
    --zone=us-central1-f

2. Create a Cloud Storage bucket

  gsutil mb -b off -l US "gs://bucket-name"

3. Back up your Microsoft SQL Server 2008 database

osql -E -Q “BACKUP DATABASE db-name TO DISK=’c:\backup\db-name.bak'”

4. Import the database into Cloud SQL for SQL Server

gcloud beta sql import bak target \
    gs://bucket-namedb-name.bak \
    --database db-name

5. Validate the imported data

/opt/mssql-tools/bin/sqlcmd -U sqlserver -S 127.0.0.1 -Q “query-string”

If you’re working with newer versions of SQL Server, check out the SQL Server 2017 to Cloud SQL for SQL Server migration guide.

Since the launch of Cloud SQL for SQL Server, we’ve heard your feedback and have continued to improve the performance and durability of the service. We expect to continue our rapid pace of innovation and feature releases to meet our customers’ needs and address feedback. Cloud SQL for SQL Server has proven itself as a key component when migrating existing enterprise applications and infrastructure.

We’re continuing to rapidly improve Cloud SQL for SQL Server to meet all of your cloud database needs. Stay tuned for features in development that can help with Active Directory integration, online migrations, and more options for replicas and machine types. 

When it comes to learning how to implement some technology, we all have our own version of what I call the “tab game”—that is, your setup for all the tabs and windows you need open at once. You may have several monitors so you can see documentation, your IDE, and terminal windows at the same time. You may have several guides and references open at once in one window to get all the information you need.

Personally, I like to work just from my laptop because I like to move around and work from various comfy spots. I think my tab game would probably enrage most devs because it involves a lot of swiping back and forth between windows *and* toggling tabs. It’s not pretty. That is, it wasn’t pretty until I discovered tutorials in the Google Cloud Console!

Jen really didn’t know about tutorials in the Google Cloud Console?

Yes, I honestly didn’t know about them! I’m sharing about it because if I can work for Google and not know, then I can’t be the only one, and it would be a shame to miss out on this because it’s a brilliant idea. Also I wrote some pretty sweet tutorials for the console, but I swear that the main reason I’m telling you is because it’s a cool thing!

There are several reasons that these tutorials are great:

• You can view the instructions and the console at the same time. No more playing the tab game!
• The tutorials include links and highlights, making it easy to find the screens and buttons you’re looking for
• You can run code from Cloud Shell, so you don’t need a separate window for an IDE
• You can use the demo data provided to try things out, or you can apply the steps to your existing projects using data that suits your app’s needs

Firestore tutorials

I’m developing a series of tutorials in the Google Cloud Console designed to take you through everything you need to know about Firestore–from manually adding data in the Google Cloud Console to triggering Cloud Functions to make changes for you. Below are links and summaries for the currently available tutorials. Check back regularly to find the latest additions as they’re released!

Add Data to Firestore

• Enable Firestore on a project
• Learn about the Firestore data model
• Add a collection of documents
• Add fields to a document
• Delete documents and collections

Updating Data in Firestore using Node.js or using Python

• Add a collection of documents
• Explore available data types
• Replace the data of document
• Replace fields in a document
• Handle special cases: incrementing, timestamps, and arrays

Reading Data from Firestore using Node.js or using Python

• Add a collection of documents
• Explore available data types
• Read a collection
• Read a single document
• Order documents
• Query documents

Transactions in Firestore using Node.js

• Add a collection of documents
• Update data without a transaction to observe issue
• Complete a transaction
• Complete a batched write

Batched Writes in Firestore using Node.js or using Python

• Use Cloud Shell and Cloud Shell Editor to write a Node.js or Python app
• Complete a batched write

Firestore triggers for Cloud Functions

• Initialize Cloud Functions using the Firebase CLI
• Write a Cloud Function triggered by a new document write to Firestore

Offline Data in Firestore

• Add data to Firestore in the Cloud console Firestore dashboard
• Create a web app that uses Firestore using the Firebase SDK
• Deploy Firestore security rules that enable access to the required data
• Enable data persistence in the web app
• Observe app behavior with and without network connection

Chime in

Is there a particular action or concept in Firestore that you’d like to see a tutorial for? Is there another Google Cloud product that you want to learn more about? Tweet @ThatJenPerson and you may just see your suggestion come to life in the Google Cloud Console!

Demand for spoken audio content such as podcasts remains robust despite the proliferation of video services and other entertainment options for consumers. Shibin Li, Co-founder of Castbox, credits growth of the global podcast platform to the following: speed and availability, market-leading features, the proliferation of smart devices to deliver audio content, and activities — such as driving and working around the house — that make consuming video difficult.

Founded in 2016 and headquartered in Beijing, China, Castbox enables users to locate, access, and create spoken audio content. Available on iOS and Android, Castbox supports 50 million podcasts, on-demand radio programs, and audiobooks in 70 languages from 175 countries. The platform hosts about 2 million users per day and is the largest podcast platform on Android.

Castbox includes a range of features that build on its core service to provide a high-quality user experience. These features include curated podcast recommendations and in-audio search.

A combination of services

At its inception, Castbox relied on a combination of a multinational cloud services, as well as Google Cloud Platform (GCP) services, including the Google BigQuery analytics data warehouse and Cloud APIs to provide programmatic interfaces with Google Cloud services, and a range of services from the Google mobile development platform Firebase.

However, as Castbox matured and its user base expanded, the business increased its reliance on Google Cloud Platform and Firebase.

“We needed to access stable cloud services as we could not tolerate long periods of downtime that would compromise the user experience,” says Li. “Furthermore, we had to support up to 50,000 concurrent connections, and potentially more in future, without disruption.”

“Based on our analysis of the data in Google BigQuery, we can determine what type of content users are listening to, how long they like to listen to it, and when they like to listen to it. This allows us to recommend similar podcasts to each user based on the preferences he or she expressed, encouraging activity on and return visits to our platform.”

—Shibin Li, Co-Founder, Castbox

Competitive differentiation

Castbox also found machine learning-powered Google Cloud Platform APIs could help deliver features, such as in-audio search, that differentiate the podcast platform from its competitors. In addition, Firebase SDKs and Firebase A/B Testing would enable Castbox to create and analyze new applications, as well as make adjustments based on user feedback. Firebase Realtime Database would allow the business to support tens of thousands of concurrent user connections.

The diligence of the Google Cloud team in advising Li and her team about forthcoming products and services also swayed Castbox towards Google technologies. The business gained the opportunity with Google to join several programs that offered early access to Google innovations.

Signature in-audio search service

Castbox now uses Google Cloud Platform services in the Tokyo, Japan, and U.S. East regions. Cloud Speech-to-Text API plays a key role in delivering Castbox’s signature in-audio search service. This service enables users to search transcriptions of audio content on the platform for words or phrases. The search results incorporate the title of the podcast and the search term in context (for example, within the sentence or sentence excerpt in which it appears). Each use of the word or phrase is time-stamped so it can easily be found. The API enables Castbox developers to apply neural network algorithms to achieve audio-to-text conversion accuracy rates of greater than 96%, while search queries typically experience latency of just 50 milliseconds.

In addition, the latency of comparison data, converting audio to text, is only about 250 milliseconds, contributing to the processing of about 12 minutes worth of audio to text in just 10 minutes. “We can process about 20 hours of audio files in one day,” Li says. “This enables us to transcribe and index all the new episodes of a podcast in that period.”

50,000 concurrent connections

With Firebase Realtime Database, Castbox now supports up to 50,000 concurrent connections to its platform with an average latency per connection of just 10 milliseconds. “Firebase Realtime Database also allows us to continue operating in offline mode, which is extremely helpful if we experience any network disruptions,” Li explains. “When we come back online again, any data is simply synchronized with the database.”

Google BigQuery and the analytics capabilities of Firebase SDKs also enable Castbox to monitor and analyze user behaviors. “Based on our analysis of the data in Google BigQuery, we can determine what type of content users are listening to, how long they like to listen to it, and when they like to listen to it,” Li explains. “This allows us to recommend similar podcasts to each user based on the preferences he or she expressed, encouraging activity on and return visits to our platform.”

“Given our queries may span up to 40 days of data, we may be analyzing up to 1,200 GB at one time. We have no problem doing this with Google BigQuery.”

—Shibin Li, Co-Founder, Castbox

Castbox also uses its analyses of Google BigQuery data to amend banners and summaries on its platform to encourage users to listen to additional content. Furthermore, the service is prepared to make surprise recommendations of content to users based on the preferences and reactions of users with similar tastes.

“We analyze a pool of data growing at up to 30 GB per day,” Li explains. “Given our queries may span up to 40 days of data, we may be analyzing up to 1,200 GB at one time. We have no problem doing this with Google BigQuery.” These analyses also support Castbox’s decision to start creating original content, such as finance and economic news, for its platform.

Checking weekly changes

Castbox does not rely only on analyzing user data to deliver a high-quality experience. The business aggregates user feedback from emails and Google Play reviews to make weekly changes to its platform. It then uses Firebase A/B Testing to check whether these changes are met with a positive user response.

“We are extremely pleased with Google Cloud Platform and Firebase. We have been able to differentiate ourselves from our competitors and provide an attractive option for users at a time when content and entertainment options are exploding. We have a great opportunity with Google to continue to improve the value of our offering to users and build engagement and loyalty.”

—Shibin Li, Co-Founder, Castbox

Castbox’s positive experiences with Google Cloud Platform are encouraging the business to grow its use of the product. “We are trying to move some more services to Google Cloud Platform because it is very stable and scalable,” Li says. The business is keen to explore the capabilities of Cloud Pub/Sub to provide low latency messaging between applications, Cloud Spanner to deliver a distributed relational database service, and Cloud Dataflow to transform and enrich data in stream and batch modes.

“We are extremely pleased with Google Cloud Platform and Firebase,” Li concludes. “We have been able to differentiate ourselves from our competitors and provide an attractive option for users at a time when content and entertainment options are exploding. We have a great opportunity with Google to continue to improve the value of our offering to users and build engagement and loyalty.”

Data security is an ongoing concern for anyone managing a data warehouse. Organizations need to control access to data, down to the granular level, for secure access to data both internally and externally. With the complexity of data platforms increasing day by day, it’s become even more critical to identify and monitor access to sensitive data. In many cases, sensitive data is co-mingled with non-sensitive data, and access restrictions to sensitive data need to be enabled based on factors like data location or presence of financial information. There may also be nuances where data is sensitive for some groups of users, while for others, it is not. 

Today, we’re pleased to announce the general availability of BigQuery row-level security, which gives customers a way to control access to subsets of data in the same table for different groups of users. Row-level security (RLS) extends the principle of least privilege access and enables fine-grained access control policies in BigQuery tables. BigQuery currently supports access controls at the project-, dataset-, table- and column-level. Adding RLS to the portfolio of access controls now enables customers to filter and define access to specific rows in a table based on qualifying user conditions—providing much needed peace of mind for data professionals. 

“Our digital transformation and migration of data to the cloud magnifies the business value we can extract from our information assets. However, granular data access control is essential to comply with international regulatory and contractual requirements. BigQuery row-level security helps us comply with data residency and export restrictions,” says Jarrett Garcia, Iron Mountain’s Enterprise Data Platform Senior Director. “It enables us to manage fine-grained access controls without replicating data. What used to take months for approval and access provisioning can now be done more efficiently and effectively. We are looking forward to implementing additional data security capabilities on the BigQuery roadmap to address other critical business use cases.”

How BigQuery row-level security works

Row-level security in BigQuery enables different user personas access to subsets of data in the same table. Customers who are currently using authorized views to enable these use cases can leverage RLS for ease of management. To express the concept of RLS, we have introduced a new entity in BigQuery called row access policy. Row access policies map a group of user principals to the rows that they can see, defined by a SQL filter predicate. 

Secure logic rules created by data owners and administrators determines which user can see which rows through the creation of a row-level access policy. The row-level access policies created on a target table by administrators or data owners are applied when a query is run on the table. One table can have multiple policies applied to it.

Below is an example, where row-level access policies have been created to filter data based on users’ “region”.

In the illustrated scenario above, row-level access policies have been created to verify a querying user’s region and to give them access only to the subset of data relevant to that region. Access policies are granted to a grantee list which support all types of IAM principles such as individual users, groups, domains or service accounts. In this example, when a user queries the table, row-level access policies are evaluated to assess which, if any, policies are applicable to that user. The group ‘sales-apac’ is granted access to view a subset of rows where region = ‘APAC’ whereas the group ‘sales-us’ is granted access to view a subset of rows where the region = ’US’. Likewise, users in both groups will see rows in both regions, and users in neither group will not see any rows.

Row-level access policies can also be created using the SESSION_USER() function to restrict access only to rows that belong to the user running the query. If none of the row access policies are applicable to the querying user, the user will have no access to the data in the table.

When a user queries a table with a row-level access policy, BigQuery displays a banner notice indicating that their results may be filtered by a row-level access policy. This notice displays even if the user is a member of the `grantee_list`.

When to put BigQuery row-level security to work

Row-level access policies are useful when you have a need to limit access to data based on filter conditions. The row-access policies’ filter predicate supports arbitrary SQL, and is conceptually similar to the WHERE clause of a SQL query. Filter predicates support the SESSION_USER() function to restrict access only to rows that belong to the user running the query. If none of the row access policies are applicable to the querying user, the user will have no access to the data in the table. Currently, the column used for filtering must be in the table, but we anticipate adding support for subqueries in the filter expression, opening up access to use cases where data is filtered based on lookup tables and calculated values. Row-level access policies can be created, updated and dropped using DDL statements. You will be able to see the list of row-level access policies applied to a table using the BigQuery schema pane in the Cloud Console,  which simplifies the management of policies per table, or by using the bq command-line tool.

Row-level security is compatible with other BigQuery security features, and can be used along with column-level security for further granularity.  Since row-level access policies are applied on the source tables, any actions performed on the table will inherit the table’s associated access policies, to ensure access to secure data is protected. Row-level access policies are applicable to every method used to access BigQuery data (API, Views, etc). 

Try it out

We’re always working to enhance BigQuery’s (and Google Cloud’s) data governance capabilities, to provide more controls around managing your data. With row-level security, we are adding deeper protections for your data. You can learn more about BigQuery row-level security in our documentation and best practices.