opensource.google.com

Menu

Introducing the Data Validation Tool

Tuesday, August 10, 2021

Data validation is a crucial step in data warehouse, database, or data lake migration projects. It involves comparing structured or semi-structured data from the source and target tables and verifying that they match after each migration step (e.g data and schema migration, SQL script translation, ETL migration, etc.)
 
Today, we are excited to announce the Data Validation Tool (DVT), an open sourced Python CLI tool that provides an automated and repeatable solution for validation across different environments. The tool uses the Ibis framework to connect to a large number of data sources including BigQuery, Cloud Spanner, Cloud SQL, Teradata, and more.

DVT connections

Why DVT?

Cross-platform data validation is a non-trivial and time-consuming effort, and many customers have to build and maintain a custom solution to perform such tasks. The DVT provides a standardized solution to validate customer's newly migrated data in Google Cloud against the existing data from their on-prem systems. DVT can be integrated with existing enterprise infrastructure and ETL pipelines to provide a seamless and automated validation.

Solution

The DVT provides connectivity to BigQuery, Cloud SQL, and Spanner as well as third-party database products and file systems. In addition, it can be easily integrated into other Google Cloud services such as Cloud Composer, Cloud Functions, and Cloud Run. DVT supports the following connection types:
  • BigQuery
  • Cloud SQL
  • FileSystem (GCS, S3, or local files)
  • Hive
  • Impala
  • MySQL
  • Oracle
  • Postgres
  • Redshift
  • Snowflake
  • Spanner
  • SQL Server
  • Teradata
The DVT performs multi-leveled data validation functions, from the table level all the way to the row level. Below is a list of the validation features:

Table level
  • Table row count
  • Group by row count
  • Column aggregation
  • Filters and limits
Column level
  • Schema/Column data type
Row level
  • Hash comparison (BigQuery only)
Raw SQL exploration
  • Run custom queries on different data sources

How to Use the DVT

The first step to validating your data is creating a connection. You can create a connection to any of the data sources listed previously. Here’s an example of connecting to BigQuery:

data-validation connections add --connection-name $MY_BQ_CONN BigQuery --project-id $MY_GCP_PROJECT

Now you can run a validation. This is how a validation between a BigQuery and a MySQL table would look:

data-validation run \

--type Column \

--source-conn $MY_BQ_CONN --target-conn $MY_SQL_CONN \

--tables-list project.dataset.source_table=database.target_table

The default validation if no aggregation is provided is a COUNT *. The tool will count the number of columns in the source table and verify it matches the count on the target table.

The DVT supports a lot of customization while validating your data. For example, you can validate multiple tables, run validations on specific columns, and add labels to your validations.

data-validation run \

--type Column \

--source-conn $MY_BQ_CONN --target-conn $MY_BQ_CONN \

--tables-list project.dataset.source_a=project.dataset.target_a,\ project.dataset.source_b=project.dataset.target_b \

--sum total_cost,revenue \

--labels owner=Mandy,description=‘finance tables’

You can also save your validation to a YAML configuration file. This way you can store previous validations and modify your validation configuration. By providing the `config-file` flag, you can generate the YAML file. Note that the validation will not execute when this flag is provided - only the file is created.

data-validation run \

--type Column \

--source-conn $MY_BQ_CONN --target-conn $MY_BQ_CONN \

-tables-list bigquery-public-data.new_york_citibike.citibike_trips \

-c citibike.yaml

Here is an example of a YAML configuration file for a GroupedColumn validation:

​​result_handler:
project_id: my-project-id
table_id: pso_data_validator.results
type: BigQuery
source: my_bq_conn
target: my_bq_conn
validations:
- aggregates:
- field_alias: count
  source_column: null
  target_column: null
  type: count
- field_alias: sum__num_bikes_available
  source_column: num_bikes_available
  target_column: num_bikes_available
  type: sum
- field_alias: sum__num_docks_available
  source_column: num_docks_available
  target_column: num_docks_available
  type: sum
filters:
- source: region_id=71
  target: region_id=71
  type: custom
grouped_columns:
- cast: null
  field_alias: region_id
  source_column: region_id
  target_column: region_id
labels:
- !!python/tuple
  - description
  - test
schema_name: bigquery-public-data.new_york_citibike
table_name: citibike_stations
target_schema_name: bigquery-public-data.new_york_citibike
target_table_name: citibike_stations
threshold: 0.0
type: GroupedColumn

Once you have a YAML configuration file, it is very easy to run the validation.

data-validation run-config -c citibike.yaml

Validation reports can be output to stdout (default) or to a result handler. The tool currently supports BigQuery as the result handler.

In order to output to BigQuery, simply add the`--bq-result-handler` or `-bqrh` flag.

data-validation run \

--type Column \

--source-conn my_bq_conn --target-conn my_bq_conn \

--tables-list bigquery-public-data.new_york_citibike.citibike_trips \

--count tripduration,start_station_name \

--bq-result-handler $YOUR_PROJECT_ID.pso_data_validator.results

Below is an example of the validation results in BigQuery. View the complete schema for validation reports in BigQuery here.

BigQuery result set

Getting Started

Ready to start integrating the DVT into your data movement processes? Check out the tool on PyPi here and contribute to the tool via GitHub. We’re actively working on new features to make the tool as useful to our customers. Happy validating!

By Neha Nene and James Fu – PSO Data Team

Google Summer of Code 2021: Mentor Stats

Monday, August 2, 2021

The global, online program, Google Summer of Code (GSoC) 2021, kicked off in May when 1,289 student developers were paired with mentors from 199 open source organizations to work on a programming project for 10 weeks.

This year we have 2,143 mentors assigned to student projects. Our mentors represent 75 countries from around the world and are a mix of past GSoC students, former Google Code-in mentors, long-time mentors and of course, new mentors.

Google Summer of Code logo

Here are more mentor statistics to check out.

Top 10 countries with the most mentors in 2021 are:

Country

Mentors

United States

554

India

302

Germany

185

United Kingdom

152

France

93

Spain

72

Switzerland

62

Canada

61

Russian Federation

49

Australia

45

  • Mentors who have participated in GSoC for 10 or more years: 80 (4%)
  • Mentors who have been a part of GSoC for 5 years or more: 211 (10%)
  • Mentors that are former GSoC students: 530 (25%)
  • Mentors that have also been involved in the Google Code-in program: 343 (16%)
  • First time GSoC mentors: 294 (14%)
Before coding began, students and mentors were introduced during the community bonding period. Together they spent a month planning their projects and milestones while students also learned about their mentor organization. During the program students gain real world experience, make connections in their newfound community, and create code that is beneficial to all. After the program ends some students decide to become mentors themselves or continue to contribute to their GSoC organization, while some blaze their own open source path. By sharing their experiences and know-how with their students, our awesome mentors represent the many possibilities within open source and in turn, continue to help build a healthy, diverse open source community.

A big ‘thank you’ to all our dedicated and enthusiastic GSoC mentors who continue to inspire our students year after year!

By Romina Vicente, Project Coordinator for the Google Open Source Programs Office

Google Summer of Code 2021: Student Stats

Monday, June 28, 2021

Google Summer of Code logo

Google Summer of Code (GSoC) is a global program focused on bringing more student developers into open source software development. On June 7th of this year, 1,286 students started their 10-week programming projects, entirely online, with 199 open source organizations. For the 2021 program, these 1,286 students joined from 69 countries across the globe, including our first student from Zambia! With the 17th year of GSoC underway, we’d like to share some program statistics about the accepted students involved in this year’s program.

Accepted Students

  • 91% are participating in their first GSoC
  • 76% are first time applicants to GSoC
  • 79% participated in open source before GSoC 2021

Degrees

  • 70% are computer science majors, 3% are Mathematics majors, 2% Physics majors, and 25% are other majors including many from engineering fields like Mechanical, Electrical, Bio, Environmental, Civil and Chemical
  • Students are studying in a variety of fields including Oceanography, Finance, Linguistics, Neuroscience, Statistics, Renewable Energy, Robotics, Geography and Digital Design

Schools / Secondary Academic Programs

GSoC participants come from 613 schools/programs that represent countries from around the world like Albania, Australia, Bolivia, Chile, China, Egypt, India, Italy, Japan, Kenya, Mexico, Norway, Poland, Sri Lanka, Tunisia, Turkey, Uganda, The United Kingdom, The United States, and Vietnam just to name a few.

All 12 schools with the most accepted students for GSoC 2021 are from India:
 

School

# of accepted students

Indian Institute of Technology, Roorkee

33

Indian Institute of Technology, Varanasi

23

Birla Institute of Technology and Science Pilani, Goa

21

Birla Institute of Technology and Science Pilani

18

National Institute Of Technology, Hamirpur

18

Indian Institute of Technology, Kanpur

17

Indian Institute of Technology, Kharagpur

17

National Institute of Technology Karnataka, Surathkal

17

International Institute of Information Technology, Hyderabad

15

Indian Institute of Technology, Bombay

13

Indian Institute of Technology, Mandi

12

Vellore Institute of Technology

12


We’re excited for all our GSoC participants as they partner with their mentors and organizations for a summer of coding and community!

Next month we’ll share more 2021 Google Summer of Code statistics, but this time, the focus will be on our amazing mentors. Stay tuned!

By Romina Vicente, Project Coordinator, Google Open Source Programs Office

Introducing the Open Source Insights Project

Thursday, June 3, 2021

Open Source Insights

Google has been working on software supply-chain security for many years, and transitive dependencies remain one of the most complex and least understood aspects. While we will be integrating this data into our Cloud and internal products in a variety of ways, we believe there is an immediate value in helping developers understand and visualize dependencies. Today, we are excited to share an exploratory visualization site: Open Source Insights, which provides an interactive view of the dependencies of open source projects.

Software development practices have evolved significantly over the last few years. Collaborative development with distributed feature development, consumption of open source and third-party packages, and publicly maintained software libraries have become commonplace, partly as a result of the widespread use of open source software. The advantages of open source are so clear that people and companies that would once have rejected OSS are now adopting it as a critical element of their environment.

But there are challenges brought by OSS too. The pace of change is electric, and it can be hard to keep up. The software packages that a large project depends on might update too frequently to keep a clear picture of what is happening. And those packages, in turn, can change their dependencies to provide new features or fix bugs. Security problems and other issues can arise unexpectedly in your project as a result, and the scale of the problem can make it all difficult to manage. Even a modest OSS project might depend on hundreds of packages.

There are tools to help, of course: vulnerability scanners and dependency audits that can help identify when a package is exposed to a vulnerability. But it can still be difficult to visualize the big picture, to understand what you depend on, and what that implies.

Open Source Insights provides a visualization of a project’s dependencies and their properties. Our exploratory website can be used to get an overview of how a particular software package is put together. Among other features, it provides interactive tools to visualize and analyze full, transitive dependency graphs. It also has a comparison tool to highlight how different versions of a package might affect your dependencies, perhaps by changing their own dependencies, adding licensing requirements, or fixing security problems.

Dependency graph for express 4.17.1

Open Source Insights shows you all this information about a package without asking you to install the package first. You can see instantly what installing a package—or an updated version—might mean for your project, how popular it is, find links to source code and other information, and then decide whether it should be installed. Insights also helps you see the importance of your project by showing the projects that depend on it: its dependents. Even a small project is important if a large number of other projects depend on it, either directly or through transitive dependencies.

Open Source Insights continuously scans millions of projects in the open source software ecosystem, gathering information about packages, including licensing, ownership, security issues, and other metadata such as download counts, popularity signals, and OpenSSF Scorecards. It then constructs a full dependency graph—transitively tracking dependencies, dependencies' dependencies, and so on—and incorporates the metadata, then publishes it so you can see how it all might affect your software. And the information it provides is continually updated.
Filtered dependency graph showing how eslint 7.27.0 depends on chalk 2.4.2 and 4.1.1

Filtered dependency graph showing how eslint 7.27.0 depends on chalk 2.4.2 and 4.1.1

This information can help visualize how software is put together, whether an update is worth doing, or how to fix a problem.

Today, Open Source Insights supports npm, Maven, Go modules, and Cargo. While we work on adding additional packaging systems, we want to hear from you: How could this data fit into your development workflow? What would make it more useful? You can reach the team at deps.dev to share your thoughts; we’ll be collecting feedback for the upcoming months and look forward to hearing your ideas on how best to improve supply-chain security.

Visit our website at deps.dev to try it out.

From the Google Cloud team: What this means for GCP’s open cloud

For users of open source software, this may be the first time you’re seeing dependency and vulnerability information in an organized and accessible way. If you’re using a managed service based on open source, it’s important to remember that you may not be affected by all vulnerabilities listed. Your provider may have taken steps to harden the products you use, and when a new vulnerability is disclosed, your provider may take responsibility for patching this on your behalf.

Google Cloud follows both these steps to help users get the benefits of open cloud while prioritizing security. Multiple layers of hardening create defense-in-depth, which helps protect services like Google Kubernetes Engine (GKE), Cloud Run and Cloud Functions from a container escape vulnerability. For components that are the user’s responsibility, we’re constantly rolling out new services—like GKE Autopilot—that automate these responsibilities.

We’re committed to protecting our customers, both through our patch rewards program and the recently launched cyber insurance partnership, the Risk Protection Program, which moves from shared responsibility to shared fate. We look forward to bringing our customers new information on their open source dependencies.

By Andrew Gerrand, Michael Goddard, Rob Pike and Nicky Ringland of the Open Source Insights Team
.