Hello, Habr! Right now, OTUS is accepting applications for a new batch of the course . As the course start approaches, we continue to share useful material with you.
Data Management
Strong Data Governance is a core principle of Twitter Engineering. As we implement BigQuery into our platform, we focus on data discovery, access control, security, and privacy.
To enhance data discovery and management, we expanded our Data Access Layer (DAL ), providing tools for both on-premises data and Google Cloud data, offering a unified interface and API for our users. As Google moves towards public availability, we will integrate it into our projects to provide users with features like column search.
BigQuery makes it easy to share and access data, but we needed to maintain some control to prevent data exfiltration. Among other tools, we chose two features:
- : a beta feature that prevents users from sharing BigQuery datasets with users outside of Twitter.
- : a control element that prevents data exfiltration and requires users to access BigQuery from known IP ranges.
We implemented authentication, authorization, and auditing (AAA) requirements for security as follows:
- Authentication: we used GCP user accounts for ad hoc queries and service accounts for ongoing queries.
- Authorization: we required each dataset to have an owner service account and a reader group.
- Auditing: we exported BigQuery stackdriver logs, which contained detailed information about query execution, into a BigQuery dataset for ease of analysis.
To ensure proper handling of Twitter user personal data, we must register all BigQuery datasets, annotate personal data, maintain proper storage, and delete (sanitize) data that has been removed by users.
We considered Google , which uses machine learning to classify and edit sensitive data, but decided to opt for manual annotation of the dataset due to accuracy. We plan to use the Data Loss Prevention API to complement user annotation.
At Twitter, we created four categories of privacy for datasets in BigQuery, listed here in order of decreasing sensitivity:
- Highly sensitive datasets are available as needed based on the principle of least privilege. Each dataset has a separate group of readers, and we will track the use of individual accounts.
- Medium sensitivity datasets (one-way pseudonyms using salted hashing) do not contain personally identifiable information (PII) and are available to a larger group of employees. This is a good balance between privacy considerations and data utility. It allows employees to perform analysis tasks, such as calculating the number of users who used a feature, without knowing who the actual users are.
- Low sensitivity datasets contain all information identifying the user. This is a good approach from a privacy standpoint, but it cannot be used for user-level analysis.
- Public datasets (released outside of Twitter) are accessible to all Twitter employees.
As for registration, we used scheduled tasks to list BigQuery datasets and register them in the Data Access Layer (), the Twitter metadata repository. Users will annotate datasets with privacy information and specify retention periods. Regarding cleanup, we are assessing the performance and cost of two options: 1. Cleaning datasets in GCS using tools like Scalding, and loading them into BigQuery; 2. Using BigQuery DML statements. We will likely use a combination of both methods to meet the requirements of different teams and data.
System functionality
Since BigQuery is a managed service, there was no need to involve the Twitter SRE team in system management or on-call duties. It was easy to ensure ample capacity for both storage and computation. We could modify slot reservations by creating tickets with Google support. We identified areas for improvement, such as self-service for slot allocation and enhancements to the monitoring dashboard, and passed these requests to Google.
Cost
Our preliminary analysis showed that the query costs for BigQuery and Presto were on par. We purchased slots at price to maintain a stable monthly cost instead of paying per TB of processed data. This decision was also influenced by user feedback, which indicated they did not want to worry about costs before running each query.
Storing data in BigQuery incurred expenses in addition to GCS costs. Tools like Scalding require datasets in GCS, and to access BigQuery, we had to upload the same datasets in BigQuery format. . We are working on connecting Scalding to BigQuery datasets, which will eliminate the need to store datasets in both GCS and BigQuery.
For rare cases that required infrequent queries on tens of petabytes, we determined that storing datasets in BigQuery was not cost-effective, and we used Presto for direct access to datasets in GCS. For this, we are looking into BigQuery External Data Sources.
Next Steps
We have seen a significant interest in BigQuery since its alpha release. We are adding more datasets and more teams to BigQuery. We are developing connectors for data analysis tools like Scalding for reading and writing to BigQuery storage. We are considering tools like Looker and Apache Zeppelin for creating enterprise quality reports and notes using BigQuery datasets.
Collaboration with Google has been very productive, and we are excited to continue and grow this partnership. We worked with Google to implement our own , to send requests directly to Google. Some of these, such as the BigQuery Parquet loader, have already been implemented by Google.
Here are some of our high-priority feature requests for Google:
- Tools for convenient data ingestion and support for the LZO-Thrift format.
- Hourly segmentation
- Improvements in access control, such as table-level, row-level, and column-level permissions.
- BigQuery with integration and support for Hive Metastore for the LZO-Thrift format.
- Enhanced data catalog integration in the BigQuery user interface
- Self-service for distributing and monitoring slots.
Conclusion
Democratizing data analysis, visualization, and machine learning in a secure way is a top priority for the Data Platform team. We identified Google BigQuery and Data Studio as tools that can help achieve this goal and released BigQuery Alpha company-wide last year.
We found that queries in BigQuery were straightforward and efficient. For ingesting and transforming data, we utilized Google tools for simple pipelines, but for more complex ones, we had to build our own Airflow infrastructure. In the area of data management, BigQuery's services for authentication, authorization, and auditing meet our needs. We needed greater flexibility for managing metadata and compliance, so we had to create our own systems. BigQuery, being a managed service, was easy to operate. Query costs were comparable to existing tools. Data storage in BigQuery incurred expenses in addition to GCS costs.
Overall, BigQuery performs well for general SQL analysis. We are seeing great interest in BigQuery, and we are working on migrating more datasets, engaging more teams, and creating more pipelines with BigQuery. Various data is used in Twitter, which will require a combination of tools like Scalding, Spark, Presto, and Druid. We intend to continue enhancing our data analysis tools and provide clear guidance to our users on how best to utilize our offerings.
Words of gratitude
I would like to thank my co-authors and teammates, Anju Dja and Will Pascucci, for their excellent collaboration and hard work on this project. I would also like to thank the engineers and managers from several teams at Twitter and Google who assisted us and the users of BigQuery at Twitter for providing valuable feedback.
If you are interested in working on these tasks, check out our on the Data Platform team.
Source: habr.com
