Boost Database Performance with AlloyDB Index Advisor: The Ultimate Optimization Tool
1375
Of your peers have already read this article.
4:00 Minutes
The most insightful time you'll spend today!
Intro
One of the time consuming tasks for DBAs is to maximize query performance. To optimize slow-running queries, they often have to do detailed analysis, understand optimizer plans, and go through a lot of trial and error before getting to the best set of indexes that help with improved query performance. However, even with expert knowledge of a database’s internals, it is challenging to choose an effective set of indexes, especially when workloads change over time. The complexity of queries including several joins, filters, and subqueries make the process of identifying the right set of indexes very challenging for DBAs. Also, the effect of an index on the query plan needs to be taken into account when considering further indexes. Creating an index may cause the query optimizer to select completely different join orders and join/scan methods. This effect is hard to predict and quantify for DBAs.
Imagine if the database itself is intelligent enough to identify such queries, and recommends creating specific B-tree indexes?
Google Cloud’s AlloyDB for PostgreSQL is a fully-managed and fully PostgreSQL-compatible database for demanding transactional and analytical workloads that provides enterprise-grade performance and availability. AlloyDB offers Index Advisor, a built-in feature that helps alleviate the guesswork of tuning query performance with deep analysis of the different parts of a query including subqueries, joins, and filters. It periodically analyzes the database workload, identifies queries that can benefit from indexes, and recommends new indexes that can increase query performance.
How the AlloyDB Index Advisor works
First, let’s review a few AlloyDB terms relevant to the Index Advisor’s work.
- PostgreSQL’s system catalog has schema metadata, such as information about tables and columns, and internal bookkeeping information.
- HypoPG is an open source PostgreSQL extension that helps with hypothetical indexes without actually creating them to see if the index helps with query execution.
- Query Optimizer generates optimal execution plan.
1. AlloyDB’s Index Advisor tracks the user query workload and analyzes it using statistics from the system catalog; it then identifies queries that could be improved significantly and potential candidate indexes for those cases. Note that there could be a large number of candidate indexes.
2. The Index Advisor evaluates each of these queries using hypothetical indexes based on the HypoPG, an open source extension and AlloyDB’s Query Optimizer.
3. The results of this evaluation are used to intelligently select the best set of indexes for the workload and to generate recommendations.
You can then simply copy and run the suggested index creation SQL commands. The Index Advisor consumes minimal resources and analyzes queries at a frequency that you define. It can also be constrained to have a user-specified maximum storage budget for the new indexes it recommends.
How to use the Index Advisor
1. Index Advisor is enabled by default. Its recommendation engine analyzes your workload at the specified frequency (every 24 hours by default) to capture any potential new index recommendations.
2. Run your queries that are representative of your workload to the instance. Index advisor tracks these queries automatically.
3. To request an index recommendation immediately, you can use the google_db_advisor_recommend_indexes() function. This function performs on-demand analysis and recommends indexes for the top 100 queries. SELECT * FROM google_db_advisor_recommend_indexes();
4. To view the recommended indexes and the queries based on periodic analysis, use the following query (also see the Usage Example section). SELECT DISTINCT recommended_indexes, queryFROM google_db_advisor_workload_report r JOIN google_db_advisor_workload_statements sON r.query_id = s.query_id;
Note that Index advisor analyzes queries issued by the connected user. For the user with the pg_read_all_stats role, it analyzes all tracked queries.
Currently, Index advisor only recommends new indexes. In future, the Index Advisor could be used to report unused indexes as well; dropping these can reduce index maintenance overhead for your transactional workload.
Evaluation
We will discuss two cases in this section.
1. Decision support benchmark: We used a sample internal benchmark that modeled a decision support system. The benchmark dataset contained 450+ columns across 24 tables. The result quantified the performance of 100+ complex analytical queries. The benchmark initially had a minimal number of indexes and the normalized total query execution time was around 200+ minutes. We then enabled the AlloyDB Index Advisor and it recommended an additional 17 indexes. Creating those indexes and re-running the queries reduced the total execution time by half to ~100 minutes (1.8x).
2. Real-world AlloyDB customer workload: This is a real-world workload from a large financial customer. They have a portfolio of complex analytical queries and business insights demand fast response times. Before using Index Advisor, DBAs had already created a few indexes that they considered to be useful to speed up these queries. Many of their queries joined 30+ tables, had 10+ filters and multiple subqueries. Those made it complex for DBAs to correctly identify the most beneficial indexes to create. AlloyDB Index Advisor analyzed these complex structures and identified four additional indexes. By adding the suggested indexes, performance of 17 queries were up by 5x – 73x. The response time of these queries dropped from seconds to milliseconds.
Usage Example
An example: We use the following example to demonstrate the use of Index Advisor. The example simulates a retail application performing analytics on its orders table.
1. Create a sample orders table.
CREATE TABLE orders (
o_orderkey bigint NOT NULL,
o_custkey int NOT NULL,
o_orderstatus "char" NOT NULL,
o_totalprice numeric(13,2) NOT NULL,
o_orderdate date NOT NULL,
o_orderpriority character varying(15) NOT NULL,
o_clerk character varying(15) NOT NULL,
o_shippriority int NOT NULL,
o_comment character varying(79) NOT NULL
);2. Insert 100K random orders into the table.
INSERT INTO orders
SELECT col, col, substr(md5(random()::text), 1, 1), random(), date '2023-01-01' + col * interval '1 hour', substr(md5(random()::text), 1, 1), concat('clerk', col), col%10, concat('comment', col)
FROM generate_series(1,1000000) g(col);3. Analyze the table to populate statistics.
ANALYZE orders;4. Run a query that counts the number of orders in the second week of Jan. You can run the query with timing on so that you can see how long it takes to run the query before and after the index is created.
\timing
SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';
Time: 42.196 ms5. Manually request an index recommendation.
SELECT * FROM google_db_advisor_recommend_indexes();
index | estimated_storage_size_in_mb
--------------------------------------------------+------------------------------
CREATE INDEX ON "public"."orders"("o_orderdate") | 25
(1 row)6. View recommended indexes for tracked queries.
SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r, google_db_advisor_workload_statements s
WHERE r.query_id = s.query_id AND recommended_indexes != '';
recommended_indexes | query
--------------------------------------------------+------------------------------------------------------------------------------------------------
CREATE INDEX ON "public"."orders"("o_orderdate") | SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';
(1 row)6. View recommended indexes for tracked queries.
SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r, google_db_advisor_workload_statements s
WHERE r.query_id = s.query_id AND recommended_indexes != ”;
recommended_indexes | query
————————————————–+————————————————————————————————
CREATE INDEX ON “public”.”orders”(“o_orderdate”) | SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= ‘2023-01-09’ and o_orderdate <= ‘2023-01-15’;
(1 row)
7. Add the recommended index.
CREATE INDEX ON "public"."orders"("o_orderdate");8. Re-run the query.
\timing
SELECT COUNT(*) FROM ORDERS WHERE o_orderdate >= '2023-01-09' and o_orderdate <= '2023-01-15';
Time: 1.403 ms (compared to 42.196ms before the index)Learn more
- To learn more about AlloyDB’s Index Advisor, see Enable and use the index advisor | AlloyDB for PostgreSQL | Google Cloud
- To learn about AlloyDB, read AlloyDB for PostgreSQL intelligent scalable storage | Google Cloud Blog
- Start building on Google Cloud with $300 in free credits and 20+ always free products. https://cloud.google.com/free
Cart.com to Transform e-Commerce for Brands Globally
8750
Of your peers have already read this article.
1:30 Minutes
The most insightful time you'll spend today!
The ecommerce playing field has been hard to navigate for most retailers, and Cart.com is on a mission to change that. Traditionally, retailers needing to run their online store, order fulfillment, customer service, marketing, and other essential activities have had to cobble together systems to get the capabilities they need – much less having access to analytics across these functions. The result is costly, siloed ecommerce operations that are difficult to manage and scale.
It’s clearly not a formula for success, yet that’s the reality facing most retailers. Cart.com, in contrast, has set out to democratize ecommerce by giving brands of all sizes the full capabilities they need to take on the world’s largest online retailers. Our end-to-end environment empowers retailers to keep more of their revenue, set up proven strategies for managing all aspects of their business, and act on valuable insights from customer data every step of the way.
Together with our talented team, we’re building a unified ecommerce platform that already provides value to many leading or up and coming brands including Whataburger, GUESS, Dr. Scholl’s, Rowing Blazers, and Howler Bros.
We’re excited about the opportunity ahead as we reimagine traditional approaches to online sales, fulfillment, marketing, accessing growth capital, providing a unified view of all ecommerce and marketing analytics, and other activities. Expectations for Cart.com are high, and we are building a company that can scale to $100B in revenue and beyond. Supported by the Startup Program by Google Cloud and Google Cloud solutions, we’re establishing a technology platform to transform all aspects of ecommerce for brands worldwide.
Partner in disruption
At Cart.com, we’re currently targeting an underserved market. Our ideal customer is beyond demonstrating product-market-fit and is now at an inflection point seeking a growth opportunity. Typically, those companies are generating between $1M and $100M in annual revenue. We’ve seen an enthusiastic response from brands and retailers as well as investors, with backing from investors in just over a year totaling $143 million in three funding rounds.
Our strategy is to build an integrated ecommerce model that combines best-of-breed solutions, many of which we gain through acquisitions and then build upon to provide a streamlined and fully integrated experience for our brands. We’ve made seven acquisitions so far to round out our online store, order fulfillment, marketing services, customer service, and we have launched some integral partnerships including easy access to growth capital through our relationship with Clearco and product protection for customers on every purchase with Extend. Instead of acquiring a data company, we’re building our data platform on Google Cloud, across each operating function for a single-view for brands to harness actionable data. We see Google Cloud as the leader for data management, analytics, machine learning (ML) and artificial intelligence (AI).
Other reasons why we’re building our business on Google Cloud include scalability, excellence, security, reach, and data analytics that are far superior to other environments.
We also feel a cultural and mission alignment with Google Cloud and envision leaning into a long-term partnership of marketing, selling, and disrupting the disruptors together. Equally important to us are the investments Google Cloud is willing to make in early-stage companies like ours. The support through the Google Cloud for Startups program has been outstanding.
Built on Google Cloud
A wide range of Google Cloud solutions provide the foundation for our platform. For instance, Cloud Pub/Sub keeps our services communicating with one another. We rely on fully managed relational databases, like Cloud SQL and Cloud Spanner, to securely handle the huge volume of brand and shopper data generated every day.
Cloud Run allowed us to develop inside of containers before our Kubernetes infrastructure was ready to go. Now, we are taking advantage of all the capabilities in Google Kubernetes Engine. BigQuery integrates with all Google Cloud solutions and offers true data streaming natively out of the box, along with Dataflow for advanced analytics. We also use Container Registry to store and manage our Docker container images. Right now, we’re testing Cloud Composer to evaluate using it for data workflow orchestration instead of Apache Airflow.
The openness of the Google Cloud environment is further enabled by Anthos, which we may deploy soon to perform data integrations quickly as we acquire more companies over the next year. For example, if we acquire a company using Azure, we can easily align it with our Google Cloud ecosystem.
Enabling ecommerce 2.0
Recently, our team has been experimenting with Google Cloud Vertex AI and the fully managed services of AI deployment and ML operations. The capabilities would save us substantial time in the management of the ML lifecycle which allows us to focus more on developing proprietary AI that will transform commerce at scale.
Because Google Cloud is so far ahead in data science, our teams benefit from deep Google Cloud expertise as we look to provide brands with unmatched insights into customers to improve services and revenue. We’re also planning to test Recommendations AI among other tools to deploy customer product recommendations and personalization as turnkey productized offerings. Moving forward, we will likely use Bigtable to aid in serving machine learning to hundreds of thousands of brands due to its low latency and scalability.
Fanatical about brand success
We know that our work with Google Cloud for Startups and use of Google Cloud solutions for best-in-class data management, analytics, ML, and AI will enable us to offer even more transformative services to brands.
We also see the opportunity to use our platform and customer insights to break down barriers between brands, enabling retailers to share information and work better together when it’s in their best interests. What we’re building today on Google Cloud is fundamentally changing what’s possible for retailers of any size everywhere.
As a startup, when recruiting talent or working with prospective customers, it helps to share our success with Google Cloud. We view them as an extension of the Cart.com team. It also validates our business as we continue building a more integrated, holistic approach to commerce that opens new opportunities and drives growth for brands worldwide.
For more details about Cart.com’s vision for unified ecommerce, check out our video.
If you want to learn more about how Google Cloud can help your startup, visit our page here to get more information about our program, and sign up for our communications to get a look at our community activities, digital events, special offers, and more.
4945
Of your peers have already watched this video.
49:30 Minutes
The most insightful time you'll spend today!
How to Migrate an Oracle Database to Google Cloud
As more enterprises migrate a growing number of workloads to the cloud, migrating databases, too, has become a key focus.
But migrating databases is also really tough. This is due to a number of different reasons.
The first one is that there’s a lot of proprietary technology and functionality that’s built into a lot of the legacy database technologies.
With Oracle, for example, that may be things like stored procedure, custom functions, Oracle RAC, so on and so forth.
This makes it really hard for database administrators to migrate to an equivalent cloud-native technology.
The second challenge is that historical on-prem databases are all quite monolithic.
These are humongous servers which don’t really fit into the cloud way of doing things.
So often, database administrators have to split these up into multiple servers in the cloud.
The third challenge, which a lot of companies underestimate, is the reliance of applications on databases.
Say, you are a company that’s been around for 10 to 15 years. As the business has grown, it’s added tens to hundreds of apps, both internal- and external-facing, that are all relying on the database.
So as you’re thinking about migration, it’s really important to consider the risks that are associated of migrating to different cloud technologies. Obviously, you would like to minimize your company’s exposure.
With that background, here are the steps, procedures, best practices, and how to solve the challenges associated with migrating on-premises Oracle databases to Google Cloud SQL PostgreSQL.
From source database migration assessment to schema conversion, data replication, and performance tuning, we will cover all of the basics required to get you started with your first Oracle to Cloud SQL migration project.
Principles to Make Organizations Data Engineering Driven
4834
Of your peers have already read this article.
4:00 Minutes
The most insightful time you'll spend today!
In the “What type of data processing organisation” paper, we examined that you can build a data culture whether your organization consists mostly of data analysts, or data engineers, or data scientists. However, the path and technologies to become a data-driven innovator are different and success comes from implementing the right tech in a way that matches a company’s culture. In this blog we will expand the data engineering driven organizations and provide how it can be built from the first principles.
Not all organizations are alike. All companies have similar functions (sales, engineering, marketing), but not all functions have the same influence on the overall business decisions. Some companies are more engineering-driven, others are sales-driven, others are marketing-driven. In practice, all companies are a mixture of all these functions. In the same way, the data strategy can be more focused on data analysts, and others on data engineering. Culture is a combination of several factors, business requirements, organizational culture, and skills within the organization.
Traditionally organizations that focused on engineering mainly came from technology driven digital backgrounds. They built their own frameworks or used programming frameworks to build repeatable data pipelines. Some of this is due to the way the data is received, the shape the data is received and the speed of the data arrival as well. If your data allows it, your organization can be more focused on data analysis, and not so much on data engineering. If you can apply an Extract-Load-Transform approach (ELT) rather than the classic Extract-Transform-Load (ETL), then you can focus on data analysis and might not need extensive data engineering capability. For example, data that can be loaded directly into the data warehouse allows data analysts to also do data engineering work and apply transformations to the data.
This does not happen so often though. Sometimes your data is messy, inconsistent, bulky, and encoded in legacy file formats or as part of legacy databases or systems, with a little potential to be actionable by data analysts.
Or maybe you need to process data in streaming, applying complex event processing to obtain competitive insights in near real time. The value of data decays exponentially with time. Most companies can process data by the next day in batch mode. However, not so many are probably obtaining such knowledge the next second data is produced.
In these situations, you need the talent to unveil the insights hidden in that amalgam of data, either messy or fast changing (or both!). And almost as importantly, you need the right tools and systems to enable that talent too.
What are those right tools? Cloud provides the scalability and flexibility for data workloads that are required in such complex situations. Long gone are the times when data teams had to beg for the resources that were required to have an impact in the business. Data processing systems are no longer scarce, so your data strategy should not generate that scarcity artificially.
In this article, we explain how to leverage Google Cloud to enable data teams to do complex processing of data, in batch and streaming. By doing so, your data engineering and science teams can have an impact when (in seconds) after the input data is generated.
Data engineering driven organizations
When the complexity of your data transformation needs is high, data engineers have a central role in the data strategy of your company, leading to data engineering driven organization. In this type of organization, data architectures are organized in three layers: business data owners, data engineers, and data consumers.
Data engineers are at the crossroads between data owners and data consumers, with clear responsibilities:
- Transporting data, enriching data whilst building integrations between analytical systems and operational systems ( as in the real time use cases)
- Parsing and transforming messy data coming from business units into meaningful and clean data, with documented metadata
- Applying DataOps, that is, functional knowledge of the business plus software engineering methodologies applied to the data lifecycle
- Deployment of models and other artifacts analyzing or consuming data
Business data owners are cross-functional domain-oriented teams. These teams know the business in detail, and are the source of data that feeds the data architecture. Sometimes these business units may also have some data-specific roles, such as data analysts, data engineers, or data scientists, to work as interfaces with the rest of the layers. For instance, these teams may design a business data owner, that is the point of contact of a business unit in everything that is related to the data produced by the unit.
At the other end of the architecture, we find the data consumers. Again, also cross-functional, but more focused on extracting insights from the different data available in the architecture. Here we typically find data science teams, data analysts, business intelligence teams, etc. These groups sometimes combine data from different business units, and produce artifacts (machine learning models, interactive dashboards, reports, and so on). For deployment, they require the help of the data engineering team so that data is consistent and trusted.
At the center of these crossroads, we find the data engineering team. Data engineers are responsible for making sure that the data generated and needed by different business units gets ingested into the architecture. This job requires two disparate skills: functional knowledge and data engineering/software development skills. This is often coined under the term DataOps (which evolved from DevOps methodologies developed within the past decades but applied to data engineering practices).
Data engineers have another responsibility too. They must help in the deployment of artifacts produced by the data consumers. Typically, the data consumers do not have the deep technical skills and knowledge to take the sole responsibility for deployment of their artifacts.This is also true for highly sophisticated data science teams. So data engineers must add other skills under their belt: machine learning and business intelligence platform knowledge. Let’s clarify this point, we don’t expect data engineers to become machine learning engineers. Data engineers need to understand ML to ensure that the data delivered to the first layer of a model ( the input ) is correct. They will also become key when delivering that first layer of data in the inference path, as here the data engineering skills around scale / HA etc really need to shine.
By taking the responsibility of parsing and transforming messy data from various business units, or for ingesting in real time, data engineers allow the data consumers to focus on creating value. Data science and other types of data consumers are abstracted away from data encodings, large files, legacy systems, complex message queue configurations for streaming. The benefits of concentrating that knowledge in a highly skilled data engineering team are clear, notwithstanding that other teams (business units and consumers) may also have their data engineers to work as interfaces with other teams. More recently, we even see squads created with members of the business units (data product owners), data engineers, data scientists, and other roles. Effectively creating complete teams with autonomy and full responsibility over a data stream, from the incoming data down to the data driven decision with impact in the business.
Reference architecture – Serverless
The number of skills required for the data engineering team is vast and diverse. We should not make it harder by expecting the team to maintain the infrastructure where they run data pipelines. They should be focusing on how to cleanse, transform, enrich, and prepare the data rather than how much memory or how many cores their solution may require.
The reference architectures presented here are based on the following principles:
- Serverless no-ops technologies
- Streaming-enabled for low time-to-insight
We present different alternatives, based on different products available in Google Cloud:
- Dataflow, the built-in streaming analytics platform in Google Cloud
- Dataproc, the Google Cloud’s managed platform for Hadoop and Spark.
- Data Fusion, a codeless environment for creating and running data pipelines
Let’s dig into these principles.
By using serverless technology we eliminate the maintenance burden from the data engineering team, and we provide the necessary flexibility and scalability for executing complex and/or large jobs. For example, scalability is essential when planning for traffic spikes during mega Friday for retailers. Using serverless solutions allows retailers to look into how they are performing during the day. They no longer need to worry about resources needed to process massive data generated during the day.
The team needs to have full control and write their own code for the data pipelines because of the type of pipelines that the team develops. This is true either for batch or streaming pipelines. In batch, the parsing requirements can be complex and no off the shelf solution works. In streaming, if the team wants to fully leverage the capabilities of the platform, they should implement all the complex business logic that is required, without artificially simplifying the complexity in exchange for some better latency. They can develop a pipeline that achieves a low latency with highly complex business logic. This again requires the team to start writing code from first principles.
However, that the team needs to write code should not imply that they need to rewrite any existing piece of code. For many input/output systems, we can probably reuse code from patterns, snippets, and similar examples. Moreover, a logical pipeline developed by a data engineering team does not necessarily need to map to a physical pipeline. Some parts of the logic can be easily reused by using technologies like Dataflow templates, and use those templates in orchestration with other custom developed pipelines. This brings the best of both worlds (reuse and rewrite), while saving precious time that can be dedicated to higher impact code rather than common I/O tasks. The reference architecture presented has another important feature: the possibility to transform existing batch pipelines to streaming.
The ingestion layer consists of Pub/Sub for real time and Cloud Storage for batch and does not require any preallocated infrastructure. Both Pub/Sub and Cloud Storage can be used for a range of cases as it can automatically scale up with the input workload.
Once the data has been ingested, our proposed architecture follows the classical division in three stages: Extract, Transform, and Load (ETL). For some types of files, direct ingestion into BigQuery (following an ELT approach) is also possible.
In the transform layer, we primarily recommend Dataflow as the data process component. Dataflow uses Apache Beam as SDK. The main advantage of Apache Beam is the unified model for batch and streaming processing. As mentioned before, the same code can be adapted to run in batch or streaming by adapting input and output. For instance, switching the input from files in Cloud Storage to messages published in a topic in Pub/Sub.
One of the alternatives to Dataflow in this architecture is Dataproc, Google Cloud’s solution for managed Hadoop and Spark clusters. The main use case is for those teams that are migrating to Google Cloud but have large amounts of inherited code in Spark or Hadoop. Dataproc enables a direct path to the cloud, without having to review all those pipelines.
Finally, we also present the alternative of Data Fusion, a codeless environment for creating data pipelines using a drag-and-drop interface. Data Fusion actually uses Dataproc as its Compute Engine, so everything we have mentioned earlier applies also to the case of Data Fusion. If your team prefers to create data pipelines without having to write any code, Data Fusion is the right tool.
So in summary, these are the three recommended components for the transform layer:
- Dataflow, powerful and versatile with a unified model for batch and streaming processing. Straightforward path to move from batch processing to streaming
- Dataproc, for those teams that want to reuse existing code from Hadoop or Spark environments.
- Data Fusion, if your team does not want to write any code.
Challenges and opportunities
Data platforms are complex. Having on top of that data responsibility the duty to maintain infrastructure is a wasteful use of valuable skills and talent. Often data teams end up managing infrastructure rather than focusing on analyzing the data. The architecture presented in this article liberates the data engineering team from having to allocate infrastructure and tweak clusters but instead to focus on providing value through data processing pipelines.
For data engineers to focus on what they do best, you need to fully leverage the cloud. A lift & shift approach from any on-premise installation is not going to provide that flexibility and liberation. You need to leverage serverless technologies. As an added advantage, serverless lets you also scale your data processing capabilities with your needs, and be able to respond to peaks of activity, however large these are.
Serverless technologies sometimes face the doubts of practitioners: will I be locked in with my provider if I fully leverage serverless? This is actually a question that you should be asking when deciding whether to set up your architecture on top of a provider.
The components presented here for data processing are based on open source technologies, and fully interoperable with other open source equivalent components. Dataflow uses Apache Beam, which not only unifies batch and streaming, but also offers a widely compatible runner. You can take your code elsewhere to any other runner. For instance, Apache Flink or Apache Spark. Dataproc is a fully managed Hadoop and Spark based on the vanilla open source components of this ecosystem. Data Fusion is actually the Google Cloud version of CDAP, an open source project.
On the other hand, for the serving layer, BigQuery is based on standard Ansi SQL. Whereas in the case of Bigtable and Google Kubernetes Engine, Bigtable is compatible at API level with HBase, and Kubernetes is an open source component.
In summary, when your components are based on open source, like the ones included in this architecture, serverless does not lock you in. The skills required to encode business logic in the form of data processing pipelines are based on engineering principles that remain stable across time. The same principles apply if you are using Hadoop, Spark, or Dataflow or UI driven ETL tooling. In addition, there are now new capabilities, such as low-latency streaming, that were not available before. A team of data engineers that learn the fundamental principles of data engineering will be able to quickly leverage those additional capabilities.
Our recommended architecture separates the logical level, the code of your applications, from the infrastructure where they run. This enables data engineers to focus on what they do best and on where they provide the highest added value. Let your Dataflow and your engineers impact your business, by adopting the technologies that liberate them and allow them to focus on adding business value. To learn more about building an unified data analytics platform, take a look at our recently published Unified Data Analytics Platform paper and Converging Architectures paper.
3785
Of your peers have already watched this video.
24:30 Minutes
The most insightful time you'll spend today!
Replacing Oracle with Cloud Spanner: What Optiva Learnt
Optiva is a Canada-based provider of business support system (BSS) and Operations Support Systems (OSS) software and services to the telecommunications vertical.
Optiva sought to improve its product offering. In the way were very tough challenges including a need for accuracy, the need to deal with very high volumes, the need for low latency.
So they looked at the market. And just by chance, they saw a Cloud Spanner press release.” And so we were looking. We were like: what can we do? How can we make this 10 times faster. And we read the Spanner press release, and we’re like: are you kidding me? This is our dream come true. We have a perfect database that can handle the transactional volume. It’s distributed, it’s consistent, multiple writings, like synchronized writing across 1,000 servers. It’s exactly what we needed to replace big bad Oracle,” says Danielle Roystone, CEO of Optiva.
To hear the entire story, watch the video!
Enhancing Data Governance through Automation with Dataplex and BigLake
2989
Of your peers have already read this article.
2:00 Minutes
The most insightful time you'll spend today!
Unlocking the full potential of data requires breaking down the silo between open-source data formats and data warehouses. At the same time, it is critical to enable data governance team to apply policies regardless of where the data happens, whether – on file or columnar storage.
Today, data governance teams have to become subject matter experts on each storage system the corporate data happens to reside on. Since February 2022, Dataplex has offered a unified place to apply policies, which are propagated across both lake storage and data warehouses in GCP. Rather than specifying policies in multiple places, bearing the cognitive load of translating policies from “what you want the storage system to do” to “how your data should behave” Dataplex offers a single point for unambiguous policy management. Now, we are making it easier for you to use BigLake.
Earlier this year, we launched BigLake into general availability, BigLake unifies data fabric between Data Lakes and Data Warehouses by extending BigQuery storage to open file formats. Today, we announce BigLake Integration with Dataplex (available in preview). This integration eliminates the configuration steps for the admin taking advantage of BigLake and managing policies across GCS and BigQuery from a unified console.
Previously, you could point Dataplex at a Google Cloud Storage (GCS) bucket, and Dataplex will discover and extract all metadata from the data lake and register this metadata in BigQuery (and Dataproc Metastore, Data Catalog) for analysis and search. With the BigLake integration capability, we are building on this capability by allowing an “upgrade” of a bucket asset, and instead of just creating external tables in BigQuery for analysis – Dataplex will create policy-capable BigLake tables!
The immediate implication is that admins can now assign column, row, and table policies to the BigLake tables auto-created by Dataplex, as with BigLake – the infrastructure (GCS) layer is separate from the analysis layer (BigQuery). Dataplex will handle the creation of a BigQuery connection and a BigQuery publishing dataset and ensure the BigQuery service account has the correct permissions on the bucket.
But wait – there’s more.
With this release of Dataplex, we are also introducing advanced logging called governance logs. Governance logs allow tracking the exact state of policy propagation to tables and columns – adding an additional level of detail going beyond the high-level “status” for the bucket and into fine-grained status and logs for tables, columns.
What’s next?
- We have updated our documentation for managing buckets and have additional detail regarding policy propagation and the upgrade process.
- Stay tuned for an exciting roadmap ahead, with more automation around policy management.
For more information, please visit:
More Relevant Stories for Your Company
Bitly’s Big Move: A Story of Massive Data Migration to Google’s Cloud Bigtable
Editor’s note: Bitly, the link & QR Code management platform, migrated 80 billion rows of link data to Cloud Bigtable. Here’s how and why they moved this data from a MySQL database, all in just six days. Our goal at Bitly is simple: Make your life easier with connections. Whether you're sharing
Analytics Hub for Secure Data Sharing and Analytics Unlocks True Data Value and Insights
Customers tell us that sharing and exchanging data with other organizations is a critical element of their analytics strategy, but it’s hamstrung by unreliable data and processes, and only getting harder with security threats and privacy regulations on the rise. Furthermore, traditional data sharing techniques use batch data pipelines that are
Cloud IoT Core Helps Businesses Leverage their IoT Data to Build a Competitive Edge
The ability to gain real-time insights from IoT data can redefine competitiveness for businesses. Intelligence allows connected devices and assets to interact efficiently with applications and with human beings in an intuitive and non-disruptive way. After your IoT project is up and running, many devices will be producing lots of
Home Depot Leverages Google Cloud’s BigQuery and DataFlow to Break Data Silos and Craft Personalized CX
The Home Depot, Inc., is the world’s largest home improvement retailer with annual revenue of over $151B. Delighting our customers—whether do-it-yourselfers or professionals—by providing the home improvement products, services, and equipment rentals they need, when they need them, is key to our success. We operate more than 2,300 stores throughout
