presto 学习

One of the biggest challenges in making use of data is avoiding the complexity of making N copies of a dataset to be used with N different analytics systems. Dataset sprawl and application silos are quicksand for productivity. And the backups of your important tables are sitting idle so perhaps you can do more? This post describes how PrestoSql can help you leverage your backup dataset to enable dev/test and analytics too.

利用数据的最大挑战之一是避免制作要与N个不同分析系统一起使用的N个数据集副本的复杂性。 数据集蔓延和应用程序孤岛是提高生产率的快速手段。 而且重要表的备份处于闲置状态，因此也许您可以做更多事情？ 这篇文章介绍了PrestoSql如何帮助您利用备份数据集来实现开发/测试和分析。

Prestosql is a distributed query engine capable of bringing SQL analytics to a wide variety of data stores, including an S3 object store like Pure FlashBlade. Running Presto on Kubernetes with FlashBlade and Pure’s CSI provisioner provide a simple, flexible, scalable, and enterprise-ready data infrastructure.

Prestosql是一个分布式查询引擎，能够将SQL分析引入各种数据存储中，包括像Pure FlashBlade这样的S3对象存储。 使用FlashBlade和Pure的CSI Provisioner在Kubernetes上运行Presto可提供简单，灵活，可扩展且可用于企业的数据基础架构。

But, PrestoSql can also be used as part of a data protection strategy to take advantage of your existing backup data copy for dev/test workloads and analytics. In this blog I will configure the Starburst operator for Presto with FlashBlade S3 and demonstrate three example usages for rapid restoration: standard disaster recovery, dev/test copies of tables, and machine learning.

但是，PrestoSql也可以用作数据保护策略的一部分，以利用您现有的备份数据副本进行开发/测试工作负载和分析。 在此博客中，我将使用FlashBlade S3为Presto配置Starburst运算符，并演示快速恢复的三种示例用法：标准灾难恢复，表的开发/测试副本以及机器学习。

PrestoSql and Pure are uniquely suited for these rapid restore use cases because they integrate easily with multiple data stores: databases, data warehouses, and data lakes. As a result, you can avoid excess data copies and leave data in the best format or system. PrestoSql queries data where it lives and Pure provides the flexible and performant storage regardless of where the data lives.

PrestoSql和Pure适用于这些快速还原用例，因为它们可以轻松地与多个数据存储集成：数据库，数据仓库和数据湖。 因此，您可以避免多余的数据副本，并以最佳格式或系统保留数据。 PrestoSql可以查询数据的存放位置，而Pure则提供了灵活而高效的存储，无论数据存放在哪里。

There is no single solution for data protection. Native tools (example for Postgresql) and third-party backup software (example: Kasten K10) are an essential part of a protection strategy. Backing up tables with PrestoSQL uniquely also makes the data copy available for other uses, such as dev/test tables or analytics and machine learning workloads. Other tools can also be used to achieve similar flows, though with less flexibility than PrestoSql, for example with Sqoop and FlashBlade.

没有单一的数据保护解决方案。 本机工具(例如Postgresql的示例)和第三方备份软件(例如： Kasten K10 )是保护策略的重要组成部分。 使用PrestoSQL唯一备份表还可以使数据副本可用于其他用途，例如开发/测试表或分析和机器学习工作负载。 其他工具也可以用于实现类似的流程，尽管灵活性比PrestoSql小，例如Sqoop和FlashBlade 。

快速还原方案 (Rapid Restore Scenarios)

Presto and FlashBlade invert standard backup workflows by focusing instead on rapid restore opportunities afforded by fast S3 storage:

Presto和FlashBlade通过专注于快速S3存储提供的快速还原机会来反转标准备份工作流程：

Restores for disaster recovery

还原以进行灾难恢复 Creation of realistic dev/test tables based on backups

根据备份创建实际的开发/测试表Analytics and machine learning on the backup copy

备份副本上的分析和机器学习

In other words, after making one copy of a dataset or table, you now have three different ways to leverage that dataset, possibly all at the same time, building on the same tool you can use for large-scale data warehousing.

换句话说，在复制数据集或表的一个副本之后，您现在可以使用三种不同的方式来利用该数据集，并且可能同时使用，并使用可用于大规模数据仓库的相同工具。

The flow of backup and possible restorations is illustrated in the figure below:

下图说明了备份和可能的还原的流程：

While disaster recovery workflows are possible, this is not the recommended primary means of protecting your databases. Instead, this is an additional layer of a data protection strategy that can both help in certain DR scenarios but also enable new use-cases with your backups.

尽管可以进行灾难恢复工作流，但这不是推荐的保护数据库的主要方法。 相反，这是数据保护策略的附加层，不仅可以在某些灾难恢复场景中提供帮助，还可以在备份中启用新的用例。

The primary database can be a wide variety of technologies; PrestoSql has a wide set of connectors for querying data on different types of storage systems.

主数据库可以是多种技术。 PrestoSql具有广泛的连接器集，可用于查询不同类型的存储系统上的数据。

Most connectors are for database-like systems that in turn store data on PersistentVolumes, backed by a CSI provisioner such as PSO. Examples include Cassandra, Elasticsearch, Kafka, MongoDB, Mysql, Oracle, Postgresql, and SQL Server.

大多数连接器用于类似数据库的系统，这些系统又将数据存储在PersistentVolumes上，并由诸如PSO之类的CSI供应商提供支持。 示例包括Cassandra，Elasticsearch，Kafka，MongoDB，Mysql，Oracle，Postgresql和SQL Server。

The Hive connector is unique: it allows Presto to directly query tables stored on an open S3 object store “data lake” such as FlashBlade. The Hive connector only uses a Hive Metastore for keeping metadata about tables on any compatible data lake. Storing backup copies of tables on FlashBlade S3 through the Hive connector has the advantage of performance for rapid restores and the flexibility of allowing other tools, e.g., Apache Spark, to also read the table data from S3.

Hive连接器是唯一的：它允许Presto直接查询存储在打开的S3对象存储“数据湖”(例如FlashBlade)中的表。 Hive连接器仅使用Hive Metastore来保存有关任何兼容数据湖上表的元数据。 通过Hive连接器将表的备份副本存储在FlashBlade S3上的优势在于可以快速恢复的性能以及允许其他工具(例如Apache Spark)也从S3读取表数据的灵活性。

Using multiple connectors in the same PrestoSql instance crucially enables you to create queries that combine data from different sources or that read from one source and write to another. For example, a CTAS statement can create a table using the Hive connector as destination and the Postgresql connector as source. This CTAS statement effectively creates a backup copy.

通过在同一PrestoSql实例中使用多个连接器，至关重要的是，您可以创建查询，这些查询合并来自不同来源的数据或从一个来源读取并写入另一个来源的数据。 例如，CTAS语句可以使用Hive连接器作为目标并使用Postgresql连接器作为源来创建表。 该CTAS语句有效地创建了备份副本。

Therefore, the cluster architecture I describe includes three layers as illustrated in the figure below:

因此，我描述的集群体系结构包括三层，如下图所示：

The PrestoSql service coordinated by Starburst

由Starburst协调的PrestoSql服务 Data services managing datasets, tables, and schemas, accessed through PrestoSql connectors for Postgresql and Hive

通过用于Postgresql和Hive的PrestoSql连接器访问的数据服务，管理数据集，表和模式Reliable data storage, block/filesystem/object, provided by Pure

可靠的数据存储，块/文件系统/对象，由Pure提供

For the examples here, I use Postgresql as the primary database with the underlying PersistentVolume provided by FlashArray via PSO.

对于此处的示例，我将Postgresql用作主数据库，并使用FlashArray通过PSO提供的基础PersistentVolume。

连接Starburst PrestoSql和FlashBlade (Connecting Starburst PrestoSql and FlashBlade)

Installing the Starburst Presto operator is straightforward following their instructions.

按照他们的指示安装Starburst Presto操作员非常简单。

The next step is to create a Presto cluster with the necessary configuration to connect to your data stores. For FlashBlade, we leverage the Hive connector to access and store tables on S3.

下一步是创建具有必要配置的Presto群集，以连接到您的数据存储。 对于FlashBlade，我们利用Hive连接器访问和存储S3上的表。

The below is an example of my configuration for Starburst and FlashBlade.

以下是我对Starburst和FlashBlade进行配置的示例。

hive: awsSecretName: presto-hive-s3-keys internalMetastore: image: pullPolicy: Always s3Endpoint: “http://10.62.64.200" internalPostgreSql: enabled: true storage: className: pure-file memory: 0.5Gi cpu: 0.5 additionalProperties: | hive.allow-drop-table=true hive.s3.path-style-access=true hive.s3.ssl.enabled=false hive.s3.endpoint=10.62.64.200

The data VIP of the FlashBlade (“10.62.64.200” in my case) is specified for s3Endpoint as well as in the additional properties section for hive.s3.endpoint. The endpoint is configured in two places; one for the hive metastore, which issues metadata-only operations, and the second for the Presto workers that use their own S3 data paths for reading and writing data.

为s3Endpoint以及在hive.s3.endpoint的其他属性部分中指定了FlashBlade的数据VIP(在我的情况下为“ 10.62.64.200”)。 端点在两个地方配置； 一个用于配置单元元存储，它仅执行元数据操作，第二个用于使用自己的S3数据路径读取和写入数据的Presto工作者。

The access keys for S3 access are encoded in the secret “awsSecretName.” I created this secret for S3 keys as follows:

用于S3访问的访问密钥被编码在秘密“ awsSecretName”中。 我为S3密钥创建了这个秘密，如下所示：

kubectl create secret generic presto-hive-s3-keys --from-literal=AWS_ACCESS_KEY_ID="$ACCESS" --from-literal=AWS_SECRET_ACCESS_KEY="$SECRET"

This configuration uses an internal Hive metastore service backed by a Postgresql database; here internal means that the Starburst operator manages it for us. In order to use FlashBlade for the Postgresql instance storage, I customize by adding the className as “pure-file.” Note that this is a wholly separate Postgresql instance as the primary database with user data.

此配置使用由Postgresql数据库支持的内部Hive Metastore服务； 在此，内部意味着Starburst运营商为我们管理它。 为了将FlashBlade用于Postgresql实例存储，我通过将className添加为“ pure-file”进行自定义。 请注意，这是一个完全独立的Postgresql实例，是带有用户数据的主数据库。

As part of the Hive configuration above, the specific part that corresponds to the internal Postgresql instance that uses PSO for storage is:

作为上述Hive配置的一部分，与使用PSO进行存储的内部Postgresql实例相对应的特定部分是：

internalPostgreSql: enabled: true storage: className: pure-file

The Starburst operator dynamically adapts the Presto cluster based on changes to the yaml specification:

Starburst运算符会根据对yaml规范的更改来动态调整Presto集群：

If I modify the yaml to change the default database used by the Postgresql connector, the starburst cluster will restart automatically and reconfigure.

如果我修改yaml以更改Postgresql连接器使用的默认数据库，则starburst群集将自动重新启动并重新配置。 If I increase the node count, the operator automatically scales out the workers.

如果增加节点数，则操作员会自动向外扩展工作人员。

To verify connectivity and start working with data, I create a Presto CLI instance to connect and start issuing queries with the following command:

为了验证连接性并开始使用数据，我创建了一个Presto CLI实例以使用以下命令连接并开始发出查询：

> COORD=example-presto # my cluster name> kubectl run presto-cli --rm -i --tty --image starburstdata/presto --presto-cli --server http://$COORD.default.svc.cluster.local:8080

I access the Presto administrative UI using port-forwarding, but the default behavior is to create a NodePort service that allows accessing the UI from any node in the Kubernetes cluster.

我使用端口转发访问Presto管理UI，但是默认行为是创建一个NodePort服务，该服务允许从Kubernetes集群中的任何节点访问UI。

> kubectl port-forward --address 0.0.0.0 service/example-presto 8080

Now, create an external table based on data already on S3 and start with some SQL queries:

现在，基于S3上已有的数据创建一个外部表，并从一些SQL查询开始：

> CREATE TABLE IF NOT EXISTS hive.default.reddit (name varchar, body varchar) WITH (format='json', external_location='s3a://joshuarobinson/reddit.json');> SELECT COUNT(*) FROM hive.default.reddit;

Presto can query existing datasets on S3 using external tables, such as the JSON data in bucket ‘joshuarobinson’ above, while also using S3 for storage of internal, Presto-managed tables.

Presto可以使用外部表查询S3上的现有数据集，例如上面存储桶“ joshuarobinson”中的JSON数据，同时也可以使用S3来存储内部的，Presto管理的表。

用普罗米修斯进行监测 (Monitoring with Prometheus)

The Starburst operator includes built-in support for monitoring Presto using Prometheus, fitting easily into Kubernetes-based monitoring configurations. Using a standard monitoring tool like Prometheus allows you to also include FlashArrays and FlashBlades with the Pure Exporter.

Starburst运营商提供了内置支持，可使用Prometheus监控Presto，轻松适应基于Kubernetes的监控配置。 使用Prometheus这样的标准监视工具，您还可以在Pure Exporter中包含FlashArray和FlashBlades。

See my previous blog for a specific example yaml file for how to monitor Starburst and FlashBlade together.

有关如何同时监视Starburst和FlashBlade的特定示例yaml文件，请参见我以前的博客。

快速还原的工作流程 (WorkFlows with Rapid Restore)

This section shows examples of how to orchestrate SQL-based workflows based on rapid restore scenarios.

本节显示了如何基于快速还原方案来编排基于SQL的工作流的示例。

初步：备份副本 (Preliminaries: The Backup Copy)

The first step is to use PrestoSql to orchestrate taking periodic backups of a database. In all of these examples, I use the Hive connector and S3 as the storage location for the table backups.

第一步是使用PrestoSql编排数据库的定期备份。 在所有这些示例中，我使用Hive连接器和S3作为表备份的存储位置。

First create a destination schema to store the backups and specify the storage location using an s3a address:

首先创建一个目标架构来存储备份，并使用s3a地址指定存储位置：

presto> CREATE SCHEMA IF NOT EXISTS hive.ph WITH (location = 's3a://joshuarobinson/warehouse/ph');

The s3a address is specified here because this is how the Hive metastore accesses S3, using the S3a adaptor from Hadoop. Note that PrestoSql reads and writes to S3 using a newer, more efficient S3 layer. The above command also assumes the bucket (“joshuarobinson”) already exists, which can be done through the FlashBlade UI or external tools like s5cmd.

此处指定s3a地址，因为这是Hive元存储库使用Hadoop的S3a适配器访问S3的方式。 请注意，PrestoSql使用更新的，更有效的S3层对S3进行读写。 上面的命令还假定存储桶(“ joshuarobinson”)已经存在，这可以通过FlashBlade UI或s5cmd之类的外部工具来完成。

You can now create a table backup using straightforward CTAS statements in Presto. In the two examples below, note that the destination table and the source tables are in different database connectors, giving you the ability to backup from a relational database (Postgresql here) to a Parquet table on S3.

现在，您可以在Presto中使用简单的CTAS语句创建表备份。 在下面的两个示例中，请注意目标表和源表位于不同的数据库连接器中，使您能够从关系数据库(此处为Postgresql)备份到S3上的Parquet表。

presto> CREATE TABLE hive.ph.ph_logs WITH (format='parquet') AS SELECT * FROM postgresql.internal.ph_logs;presto> CREATE TABLE hive.ph.ph_logs WITH (format='parquet', bucketed_by=ARRAY['id'], bucket_count=50) AS SELECT * FROM postgresql.internal.ph_logs;

用法1：基本还原 (Usage 1: Basic Restore)

As a first step, I can reverse the original backup and re-create my table in the postgresql instance as a CTAS from the Parquet data stored on S3.

第一步，我可以撤消原始备份，并根据存储在S3上的Parquet数据以CTAS的形式在postgresql实例中重新创建我的表。

presto> CREATE TABLE postgresql.restored.ph_logs AS SELECT * FROM hive.ph.ph_logs;CREATE TABLE: 32170139 rowsQuery 20200501_160205_00202_si7m6, FINISHED, 6 nodesSplits: 106 total, 106 done (100.00%)6:04 [32.2M rows, 97.4MB] [88.5K rows/s, 274KB/s]

A restore like this is useful as part of a “defense in depth” approach to disaster recovery circumstances; human error being the most common type of disaster.

这样的还原对于灾难恢复情况的“深度防御”方法非常有用。 人为错误是最常见的灾难类型。

用法2a：为开发/测试用法克隆表 (Usage 2a: Cloning a Table for Dev/Test Usage)

In this example, we will recreate the table in a different, test database instance named “postgresql-dev.” This will create a separate copy of the database suitable for testing new application versions.

在此示例中，我们将在另一个名为“ postgresql-dev”的测试数据库实例中重新创建表。 这将创建适合于测试新应用程序版本的数据库的单独副本。

presto> CREATE TABLE “postgresql-dev”.public.atop_mem AS SELECT * FROM hive.pgs.atop_mem

The following screenshot from the FlashBlade UI shows how quickly my example 2GB table can be cloned. The data is immediately read in from FlashBlade S3 to Presto and then the bottleneck is how quickly the destination database can be updated. Automation around dev/test workflows requires clones to complete seconds, not hours, so that test feedback is responsive and therefore useful.

FlashBlade UI的以下屏幕截图显示了可快速克隆示例2GB表的速度。 数据立即从FlashBlade S3读入Presto，然后瓶颈是目标数据库的更新速度。 围绕开发人员/测试工作流程的自动化要求克隆完成几秒钟而不是几小时，以便测试反馈具有响应性，因此很有用。

用法2b：安全降级 (Usage 2b: Secure Downgrade)

Taking the dev/test workflow further, the restoration of the table can also involve useful transformations!

使开发/测试工作流程更进一步，表的还原还可能涉及有用的转换！

Creating databases for test purposes often requires an extra step to sufficiently anonymize sensitive data, i.e. a “secure downgrade.” This is necessary because testing is often done in a different security environment and test code might introduce security bugs.

创建用于测试目的的数据库通常需要额外的步骤来充分匿名化敏感数据，即“安全降级”。 这是必要的，因为测试通常是在不同的安全环境中完成的，并且测试代码可能会引入安全漏洞。

CREATE TABLE “postgresql-dev”.public.customer_logs ASSELECT to_hex(md5(to_utf8(customer))) AS customer_anon, path, filenameFROM hive.pgs.ph_logs

The query above creates a new table using a transformation; the path and filename columns are copied as-is but the “customer” name is transformed using the “md5” SQL function. The resulting table contains a hashed string as the anonymized customer name, more suitable for working in a dev/test environment where security controls are necessarily less strict.

上面的查询使用转换创建了一个新表； path和filename列按原样复制，但使用“ md5” SQL函数转换“客户”名称。 结果表包含一个哈希字符串作为匿名客户名称，更适合在安全控制不一定严格的开发/测试环境中工作。

This is a simple example that illustrates the ability to transform the source data as part of the “clone” operation to make it more suitable for test usage. Prestosql enables you to use any standard SQL functionality to apply transformation during the table clone or restore.

这是一个简单的示例，说明了将源数据转换为“克隆”操作的一部分以使其更适合测试使用的能力。 Prestosql使您可以使用任何标准SQL功能在表克隆或还原期间应用转换。

用法3a：“备份”报告 (Usage 3a: Reports on the “Backup”)

Now you can build dashboards using Presto SQL queries against the tables on S3. Using standard SQL, these can be the same reporting queries you were using before, just targeting the tables stored on S3.

现在，您可以使用Presto SQL查询针对S3上的表构建仪表板。 使用标准SQL，这些可以与以前使用的报表查询相同，仅针对存储在S3上的表。

An example SQL query to create a summary report in one of my tables:

在我的一个表中创建摘要报告的示例SQL查询：

presto> SELECT customer,system,hostname,AVG(ticks_idle / (ticks_per_sec * cpu_count)) as busy FROM hive.pgs.atop_cpu GROUP BY customer,system,hostname ORDER BY busy DESC LIMIT 10;

The advantage of this approach is that you can offload queries from running on your primary database where they can negatively impact performance. You can also leverage columnar encoding formats like Parquet for far faster analytics queries.

这种方法的优点是，您可以减轻查询在主数据库上运行的负担，这会对性能产生负面影响。 您还可以利用像Parquet这样的列式编码格式来更快地进行分析查询。

用法3b：机器学习及其他 (Usage 3b: Machine Learning and Beyond)

But there are many types of analytics not well-suited for SQL, like machine learning based on your table data. Conveniently, Presto stores tables in open formats like Parquet such that tools like Apache Spark can easily read and access that data on FlashBlade.

但是，有许多类型的分析方法都不适合SQL，例如基于表数据的机器学习。 方便地，Presto以Parquet之类的开放格式存储表，以便诸如Apache Spark之类的工具可以轻松地在FlashBlade上读取和访问该数据。

Apache Spark works well with Presto tables on S3 because Spark easily loads the data with schema included!

Apache Spark与S3上的Presto表配合得很好，因为Spark可以轻松加载包含模式的数据！

tablepath="s3a://joshuarobinson/warehouse/pgs/atop_cpu/"tb = spark.read.parquet(tablepath)tb.printSchema()root | — customer: string (nullable = true) | — system: string (nullable = true) | — hostname: string (nullable = true) | — epoch: long (nullable = true)…

Now that a dataframe is loaded, I construct a machine learning pipeline that uses a random forest classifier to try and make sense of my data.

现在已经加载了数据帧，我将构建一个机器学习管道，该管道使用随机森林分类器来尝试理解我的数据。

# First, simple transformations of the datatb = tb.withColumn(“total_ticks”, col(“cpu_count”) * col(“ticks_per_sec”) * col(“interval”))tb_norm = tb.withColumn(“pct_system”, col(“ticks_system”) / col(“total_ticks”)) \ .withColumn(“pct_user”, col(“ticks_user”) / col(“total_ticks”)) \ .withColumn(“pct_user_nice”, col(“ticks_user_nice”) / col(“total_ticks”)) \ .withColumn(“pct_wait”, col(“ticks_wait”) / col(“total_ticks”)) \ .withColumn(“pct_irq”, col(“ticks_irq”) / col(“total_ticks”)) \ .withColumn(“pct_irqsoft”, col(“ticks_irqsoft”) / col(“total_ticks”)) \ .withColumn(“pct_guest”, col(“ticks_guest”) / col(“total_ticks”))# Create the feature vector and labels and the ML training pipeline.labelIndexer = pyspark.ml.feature.StringIndexer(inputCol=”customer”, outputCol=”indexedLabel”).fit(tb_norm)assembler = pyspark.ml.feature.VectorAssembler(inputCols=[“pct_system”, “pct_user”, “pct_user_nice”, “pct_wait”, “pct_irq”, “pct_irqsoft”, “pct_guest”],outputCol=”features”)rf = pyspark.ml.classification.RandomForestClassifier(labelCol=”indexedLabel”, featuresCol=”features”, numTrees=10)labelConverter = pyspark.ml.feature.IndexToString(inputCol=”prediction”, outputCol=”predictedLabel”, labels=labelIndexer.labels)pipeline = pyspark.ml.Pipeline(stages=[labelIndexer, assembler, rf, labelConverter])model = pipeline.fit(trainingData)predictions = model.transform(testData)

This is a simple example using SparkML, but note that using data from a Presto table is straightforward: the schema is automatically detected and then all SparkML code proceeds as normal with no extra hooks or boilerplate needed. Sharing the data between Presto and SparkML is trivial because of open data formats like Parquet and a flexible data hub built on FlashBlade S3.

这是一个使用SparkML的简单示例，但请注意，使用Presto表中的数据非常简单：自动检测该架构，然后所有SparkML代码照常进行，而无需额外的钩子或样板。 由于开放数据格式(例如Parquet)和基于FlashBlade S3构建的灵活数据中心，在Presto和SparkML之间共享数据非常简单。

概要 (Summary)

Pure FlashBlade and Starburst PrestoSql provide a modern analytics infrastructure uniquely capable of spanning multiple databases and data lakes. Because of their flexibility and performance, making a backup copy of a table leads to multiple ways to leverage rapid restores. A single data table on FlashBlade S3 can power 1) standard disaster recovery, 2) creation of dev/test instances, and 3) advanced analytics and machine learning.

Pure FlashBlade和Starburst PrestoSql提供了现代的分析基础架构，该基础架构独特地能够跨越多个数据库和数据湖。 由于其灵活性和性能，制作表的备份副本会导致多种方式来利用快速还原。 FlashBlade S3上的单个数据表可以支持1)标准灾难恢复，2)创建开发/测试实例以及3)高级分析和机器学习。

翻译自: https://medium.com/@joshua_robinson/presto-and-fast-object-putting-backups-to-use-for-devops-and-machine-learning-s3-46876eef4ffa

presto 学习