redshift教程

目錄 (Table of Contents)

• Introduction

  介紹

• Setting up an Amazon Redshift Datasource

  設(shè)置Amazon Redshift數(shù)據(jù)源

• Querying Data From Your Datasource

  從數(shù)據(jù)源查詢(xún)數(shù)據(jù)

• Analyzing and Visualizing Your Data

  分析和可視化您的數(shù)據(jù)

• Adding Drilldowns to Your Visualizations

  在可視化中添加明細(xì)

• Querying Your Data Using Search-Based Analytics

  使用基于搜索的分析查詢(xún)數(shù)據(jù)

• Summary

  摘要

介紹 (Introduction)

Amazon Redshift is Amazon’s cloud-based relational database management system (RBDMS). Like most of Amazon’s offerings, Amazon Redshift is very popular, and for good reason: not only is it currently the fastest cloud data warehouse, but it gets faster every year.

Amazon Redshift是Amazon的基于云的關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)(RBDMS)。 與Amazon的大多數(shù)產(chǎn)品一樣，Amazon Redshift也非常受歡迎，這有充分的理由：它不僅是目前最快的云數(shù)據(jù)倉(cāng)庫(kù)，而且每年都在增長(zhǎng)。

Here at Knowi, we offer broad native integration to Amazon Redshift for analytics and reporting. This enables our users to leverage the speed and scalability of Redshift without any constraints, and to quickly analyze data from Redshift and form valuable insights. If you’re interested in learning how to use Knowi to analyze data from Amazon Redshift, you’ve come to the right place.

在Knowi，我們?yōu)锳mazon Redshift提供了廣泛的本機(jī)集成，以進(jìn)行分析和報(bào)告 。 這使我們的用戶(hù)可以不受限制地利用Redshift的速度和可擴(kuò)展性，并快速分析Redshift中的數(shù)據(jù)并形成有價(jià)值的見(jiàn)解。 如果您有興趣學(xué)習(xí)如何使用Knowi分析來(lái)自Amazon Redshift的數(shù)據(jù)，那么您來(lái)對(duì)地方了。

設(shè)置您的Amazon Redshift數(shù)據(jù)源 (Setting up Your Amazon Redshift Datasource)

After logging into your Knowi trial account, the first thing you’re going to do is connect to an Amazon Redshift Datasource and confirm that your connection is successful. This is how:

登錄到Knowi試用帳戶(hù)后 ，要做的第一件事是連接到Amazon Redshift數(shù)據(jù)源并確認(rèn)連接成功。 這是這樣的：

1. Find “Data sources” on the panel on the left side of your screen and click on it.

1.在屏幕左側(cè)的面板上找到“數(shù)據(jù)源”，然后單擊它。

2. Head down to “Data Warehouses” and click on Amazon Redshift.

2.轉(zhuǎn)到“數(shù)據(jù)倉(cāng)庫(kù)”，然后單擊Amazon Redshift。

3. We don’t need to change any of the parameters here; Knowi automatically enters all of them for us. Just click “Test Connection” at the bottom of your screen.

3.我們不需要在這里更改任何參數(shù)； Knowi會(huì)自動(dòng)為我們輸入所有信息。 只需單擊屏幕底部的“測(cè)試連接”。

4. Once you’ve confirmed that your connection was successful, click “Save.”

4.確認(rèn)連接成功后，單擊“保存”。

Your data source is now set up. Good work!

現(xiàn)在，您的數(shù)據(jù)源已建立。 干得好！

從數(shù)據(jù)源查詢(xún)數(shù)據(jù) (Querying Data From Your Datasource)

Your datasource is now set up, which means it’s time to start querying your data. Here’s how to do this:

現(xiàn)在，您的數(shù)據(jù)源已建立，這意味著該開(kāi)始查詢(xún)數(shù)據(jù)了。 這樣做的方法如下：

1. After saving your datasource, you should’ve received an alert at the top of your page that said “Datasource Added. Configure Queries.” Click on the word queries. (Otherwise, you can go back to the panel on the left side of your screen, go right below “Data Sources” and click on “Queries.” Then select “New Query +” from the top right.)

1.保存數(shù)據(jù)源后，您應(yīng)該在頁(yè)面頂部收到一條警報(bào)，提示“已添加數(shù)據(jù)源。 配置查詢(xún)。” 單擊單詞查詢(xún)。 (否則，您可以返回屏幕左側(cè)的面板，轉(zhuǎn)到“數(shù)據(jù)源”下方，然后單擊“查詢(xún)”。然后從右上方選擇“新建查詢(xún)+”。)

2. Give your report a name inside “Report Name*” on the very top left of your screen. We’re going to analyze an email campaign here, so let’s call this one “Email Campaign.”

2.在屏幕左上方的“報(bào)告名稱(chēng)*”中為報(bào)告命名。 我們將在這里分析電子郵件活動(dòng)，因此我們將其稱(chēng)為“電子郵件活動(dòng)”。

3. In your Query Builder, click inside the “Tables” bar. Scroll down to “public.demo_sent” and click on that. This will automatically set up a Redshift query that returns the data within this table.

3.在查詢(xún)生成器中，單擊“表”欄內(nèi)。 向下滾動(dòng)到“ public.demo_sent”，然后單擊。 這將自動(dòng)設(shè)置Redshift查詢(xún)，該查詢(xún)返回此表中的數(shù)據(jù)。

4. Head over to the bottom left hand of your screen and click on the blue “Preview” button in order to preview the data. You should see the results of an email campaign that includes various data such as the number of emails sent, opened, and clicked on, as well as the message type and the customer.

4.轉(zhuǎn)到屏幕的左下角，然后單擊藍(lán)色的“預(yù)覽”按鈕以預(yù)覽數(shù)據(jù)。 您應(yīng)該看到一個(gè)電子郵件活動(dòng)的結(jié)果，其中包含各種數(shù)據(jù)，例如發(fā)送，打開(kāi)和單擊的電子郵件數(shù)，以及消息類(lèi)型和客戶(hù)。

5. Once you’ve looked over your data, scroll to the bottom right corner of your screen and click the green “Save & Run Now” button.

5.查看完數(shù)據(jù)后，滾動(dòng)到屏幕的右下角，然后單擊綠色的“立即保存并立即運(yùn)行”按鈕。

As soon as your query was successfully completed, Knowi automatically saved the results of your query as a virtual dataset and then stored the results of that query as a dataset within its elastic data warehouse. Knowi does this every time you run a query.

成功完成查詢(xún)后，Knowi會(huì)自動(dòng)將查詢(xún)結(jié)果保存為虛擬數(shù)據(jù)集，然后將該查詢(xún)結(jié)果存儲(chǔ)為彈性數(shù)據(jù)倉(cāng)庫(kù)中的數(shù)據(jù)集。 每當(dāng)您運(yùn)行查詢(xún)時(shí)，Knowi都會(huì)這樣做。

分析和可視化您的數(shù)據(jù) (Analyzing and Visualizing Your Data)

Although you spent a little bit of time looking over your data, it’s unlikely that you learned anything from it in the format that it was in. There are a ton of things that we could figure out with our data, but let’s say we had to answer a burning question: do emails sent to certain customers have a higher conversion rate? Knowi allows us to efficiently answer this question and then visualize our results with the following steps:

盡管您花了一些時(shí)間查看數(shù)據(jù)，但您不太可能以其所使用的格式從數(shù)據(jù)中學(xué)到任何東西。我們可以從數(shù)據(jù)中找出很多東西，但是我們不得不回答一個(gè)緊迫的問(wèn)題：發(fā)送給某些客戶(hù)的電子郵件的轉(zhuǎn)換率更高嗎？ Knowi使我們能夠有效地回答這個(gè)問(wèn)題，然后通過(guò)以下步驟可視化我們的結(jié)果：

1. Head to the top of the panel on the left side of your screen and click on “Dashboards.” Click the orange plus icon and name your dashboard. We’ll call this one “Email Visualization.”

1.轉(zhuǎn)到屏幕左側(cè)面板的頂部，然后單擊“儀表板”。 單擊橙色加號(hào)圖標(biāo)，然后為您的儀表板命名。 我們將其稱(chēng)為“電子郵件可視化”。

2. Head back to the panel, just below “Dashboards,” and click on “Widgets.” Select the “Email Campaign” widget that you just created and drag it onto your dashboard.

2.回到“儀表板”下方的面板，然后單擊“窗口小部件”。 選擇您剛剛創(chuàng)建的“電子郵件活動(dòng)”小部件，然后將其拖動(dòng)到儀表板上。

3. Right now, the visualization that you see is just a data grid. We’re going to change this to something a little easier on the eyes, but first we have to add the metric that we’re looking for. In order to do this, scroll to the top right corner of your widget. Click on the 3 dot icon, then scroll down to “Analyze” and click on it.

3.現(xiàn)在，您看到的可視化只是一個(gè)數(shù)據(jù)網(wǎng)格。 我們將改變它，使之看起來(lái)更輕松一些，但是首先我們必須添加我們要尋找的指標(biāo)。 為此，請(qǐng)滾動(dòng)至小部件的右上角。 單擊3點(diǎn)圖標(biāo)，然后向下滾動(dòng)到“分析”并單擊它。

4. Head to the top left corner of your screen and find “+Add Function.” The function that we’re looking to create is very simple: it’s called “conversion rate” and it’s calculated by dividing “conversions” by “sent.” In order to calculate this, click on “+Add Function,” then set “Name” as Conversion Rate and “Operation” as (conversions/sent)*100.

4.轉(zhuǎn)到屏幕的左上角，然后找到“ +添加功能”。 我們希望創(chuàng)建的功能非常簡(jiǎn)單：稱(chēng)為“轉(zhuǎn)化率”，通過(guò)將“轉(zhuǎn)化”除以“已發(fā)送”來(lái)計(jì)算。 為了計(jì)算該值，請(qǐng)單擊“ +添加功能”，然后將“名稱(chēng)”設(shè)置為“轉(zhuǎn)換率”，將“操作”設(shè)置為(轉(zhuǎn)換/發(fā)送)* 100。

5. Right now, all we see is the conversion rate of each individual email campaign. What we want to see is the conversion rate of all email campaigns grouped by customer, and we also want our data sorted by conversion rate. First, we need to drag the “customer” bar from the left side of the screen over to the “Grouping/Dimensions:” box and let go.

5.現(xiàn)在，我們看到的只是每個(gè)電子郵件廣告系列的轉(zhuǎn)化率。 我們要查看的是按客戶(hù)分組的所有電子郵件廣告系列的轉(zhuǎn)化率，我們還希望我們的數(shù)據(jù)按轉(zhuǎn)化率排序。 首先，我們需要將“客戶(hù)”欄從屏幕左側(cè)拖到“分組/維度：”框中，然后放開(kāi)。

6. Now we just need to drag our new “Conversion Rate” metric from “Fields/Metrics:” over to “Sort by:” and change the direction to descending in order to sort our data from the highest conversion rate to the lowest. As you can see, Facebook emails have a conversion rate of just over 1%, while Netflix emails have a conversion rate of less than 0.5%.

6.現(xiàn)在，我們只需要將新的“轉(zhuǎn)化率”指標(biāo)從“字段/指標(biāo)：”拖到“排序依據(jù)：”，然后將方向更改為下降，以便將數(shù)據(jù)從最高轉(zhuǎn)化率降到最低。 如您所見(jiàn)，Facebook電子郵件的轉(zhuǎn)換率剛剛超過(guò)1％，而Netflix電子郵件的轉(zhuǎn)換率不到0.5％。

7. Now it’s time to visualize everything. Head back to the top of your screen and click on “Visualization.” Change your visualization type from “Data Grid” to “Column.”

7.現(xiàn)在是時(shí)候可視化所有內(nèi)容了。 回到屏幕頂部，然后單擊“可視化”。 將可視化類(lèi)型從“數(shù)據(jù)網(wǎng)格”更改為“列”。

8. Now you can see each customer ranked by the conversion rate of their emails. Head to the top right corner of your screen and click on the “Clone” icon which looks like two pieces of paper. Name this one “Email Campaign — Conversion Rates” and click the orange “Add to Dashboard” button.

8.現(xiàn)在，您可以看到按客戶(hù)的電子郵件轉(zhuǎn)換率排名的每個(gè)客戶(hù)。 轉(zhuǎn)到屏幕的右上角，然后單擊看起來(lái)像兩張紙的“克隆”圖標(biāo)。 將此命名為“電子郵件廣告系列-轉(zhuǎn)化率”，然后單擊橙色的“添加到儀表板”按鈕。

Just like that, you’ve turned your raw data into a visualization that contains valuable information. The knowledge that Facebook’s conversion rate is about two and a half times higher than Netflix’s may be factored into future decision making.

就像這樣，您已將原始數(shù)據(jù)轉(zhuǎn)換為包含有價(jià)值信息的可視化文件。 Facebook的轉(zhuǎn)換率大約是Netflix的兩倍半，這可能是將來(lái)的決策依據(jù)。

在可視化中添加明細(xì) (Adding Drilldowns to Your Visualization)

The next step to improving our visualization is to make it more interactive and navigable by adding drilldowns. Drilldowns are a powerful feature within Knowi that allow the user to take a deeper dive into a filtered section of the raw data with just one click. Here’s how we’ll add a drilldown to our widget:

改善可視化效果的下一步是通過(guò)添加向下鉆取使其更具交互性和導(dǎo)航性。 向下鉆取是Knowi中的一項(xiàng)強(qiáng)大功能，使用戶(hù)只需單擊一下即可深入了解原始數(shù)據(jù)的過(guò)濾部分。 這是我們向小部件添加明細(xì)的方法：

1. Click on the 3 dot icon in the top right corner of your new widget, scroll down to “Drilldowns” and click on it.

1.單擊新窗口小部件右上角的3點(diǎn)圖標(biāo)，向下滾動(dòng)到“向下鉆取”，然后單擊它。

2. Set your Drilldown type as “Widget,” set it to drill into “Email Campaign” when “Customer” is clicked, and set customer = customer in your optional drilldown filters. Click the orange “Save” button at the bottom right corner of the Drilldowns popup.

2.將“向下鉆取”類(lèi)型設(shè)置為“窗口小部件”，將其設(shè)置為在單擊“客戶(hù)”時(shí)鉆入“電子郵件活動(dòng)”，然后在可選的向下鉆取過(guò)濾器中設(shè)置“客戶(hù)=客戶(hù)”。 單擊“向下鉆取”彈出窗口右下角的橙色“保存”按鈕。

3. Test it out by clicking on Facebook, the customer whose conversion rate is the highest. As you can see, this returns every campaign where Facebook was the customer. Then get back to your original visualization, head back to the top right corner of your widget and click on the left arrow icon in the middle of that corner.

3.通過(guò)單擊Facebook(轉(zhuǎn)化率最高的客戶(hù))進(jìn)行測(cè)試。 如您所見(jiàn)，這將返回以Facebook為客戶(hù)的每個(gè)廣告系列。 然后回到原始的可視化效果，回到小部件的右上角，然后單擊該角中間的向左箭頭圖標(biāo)。

使用基于搜索的分析查詢(xún)數(shù)據(jù) (Querying Your Data with Search-Based Analytics)

Your dashboard is set up, which means you’re fully prepared to query your data using search-based analytics. This means you’re ready to share your dashboard and your data with anybody who speaks English, even if they’re unfamiliar with Knowi. Here’s how to query your data using search-based analytics:

儀表盤(pán)已設(shè)置完畢，這意味著您已準(zhǔn)備好使用基于搜索的分析來(lái)查詢(xún)數(shù)據(jù)。 這意味著您已經(jīng)準(zhǔn)備好與任何會(huì)說(shuō)英語(yǔ)的人共享儀表板和數(shù)據(jù)，即使他們不熟悉Knowi。 以下是使用基于搜索的分析查詢(xún)數(shù)據(jù)的方法：

1. Head to the top right corner of your original “Email Campaign” widget and click on the 3 dot icon. Scroll down and click on “Analyze.”

1.轉(zhuǎn)到原始“電子郵件活動(dòng)”窗口小部件的右上角，然后單擊3點(diǎn)圖標(biāo)。 向下滾動(dòng)并單擊“分析”。

2. Let’s say you want to monitor email activity by month in order to see if things looked any different in different months. In order to do this, head to the search bar at the top of your screen and type “total sent, total opened, total clicks, total conversions by month” and then hit enter. Knowi’s natural language processing will quickly provide you with what you’re looking for.

2.假設(shè)您要按月監(jiān)視電子郵件活動(dòng)，以查看不同月份的情況是否有所不同。 為此，請(qǐng)轉(zhuǎn)到屏幕頂部的搜索欄，然后輸入“發(fā)送總數(shù)，打開(kāi)總次數(shù)，點(diǎn)擊總數(shù)，每月總轉(zhuǎn)化次數(shù)”，然后按Enter。 Knowi的自然語(yǔ)言處理將Swift為您提供所需的內(nèi)容。

3. Now it’s time to visualize this data. Head back to “Visualization” and set the visualization type to “Area.” This will show us the total number of emails sent, and the total number of conversions per month. The conversions are so low that it’s hard to see any movement in the number with our naked eye, but that’s okay.

3.現(xiàn)在是時(shí)候可視化這些數(shù)據(jù)了。 返回“可視化”并將可視化類(lèi)型設(shè)置為“區(qū)域”。 這將向我們顯示已發(fā)送的電子郵件總數(shù)以及每月的轉(zhuǎn)換總數(shù)。 轉(zhuǎn)換率如此之低，以至于我們?nèi)庋酆茈y看到數(shù)字的變化，但這沒(méi)關(guān)系。

4. Head back to the top right and click on the “clone” icon once again. Name this widget “Sent and Conversions — Area,” clone it, and then add it to your dashboard.

4.回到右上角，然后再次單擊“克隆”圖標(biāo)。 將此小部件命名為“發(fā)送和轉(zhuǎn)換-區(qū)域”，將其克隆，然后將其添加到您的信息中心。

5. Last, head back to your dashboard. Drag your new “Email Campaign — Area Visualization” widget to the top of your dashboard, which will bring the original “Email Campaign” widget to the bottom.

5.最后，回到儀表板。 將新的“電子郵件活動(dòng)-區(qū)域可視化”小部件拖動(dòng)到儀表板的頂部，這會(huì)將原始的“電子郵件活動(dòng)”小部件帶到底部。

This data conveys another valuable piece of insight: these email campaigns receive a low number of opens and clicks and an extremely low number of conversions for every email that they send. While these numbers remain consistently low every month, they do seem to increase alongside the number of total emails sent.

這些數(shù)據(jù)傳達(dá)了另一個(gè)有價(jià)值的見(jiàn)解：這些電子郵件廣告系列收到的打開(kāi)和點(diǎn)擊次數(shù)很少，而發(fā)送的每封電子郵件的轉(zhuǎn)化次數(shù)也很少。 盡管這些數(shù)字每個(gè)月始終保持較低水平，但似乎隨著發(fā)送的電子郵件總數(shù)的增加而增加。

It’s also important to remember that we didn’t need extensive coding knowledge or experience with Knowi to do what we just did. The low barriers to usage here makes Knowi’s dashboards accessible to any curious English speaker.

同樣重要的是要記住，我們不需要廣泛的編碼知識(shí)或豐富的Knowi經(jīng)驗(yàn)即可完成我們剛剛做的事情。 此處使用的障礙很低，因此任何好奇的英語(yǔ)使用者都可以使用Knowi的儀表板。

摘要 (Summary)

In summary, we connected to an Amazon Redshift Datasource and made a query on our new datasource. This stored the results of our query in Knowi’s elastic data warehouse. We then analyzed and visualized our data, and added drilldowns to our visualization that allow the user to drill in on a filtered section of the raw data that they’d like to learn more about. Lastly, we used search-based analytics to answer another question and visualize our answer.

總而言之，我們連接到Amazon Redshift數(shù)據(jù)源，并對(duì)新數(shù)據(jù)源進(jìn)行了查詢(xún)。 這將查詢(xún)結(jié)果存儲(chǔ)在Knowi的彈性數(shù)據(jù)倉(cāng)庫(kù)中。 然后，我們對(duì)數(shù)據(jù)進(jìn)行了分析和可視化，并在可視化中添加了向下鉆取，從而使用戶(hù)可以深入了解他們想了解更多信息的原始數(shù)據(jù)的過(guò)濾部分。 最后，我們使用了基于搜索的分析來(lái)回答另一個(gè)問(wèn)題并可視化我們的答案。

翻譯自: https://towardsdatascience.com/analyzing-visualizing-amazon-redshift-data-tutorial-239aa6443d43

redshift教程

總結(jié)

以上是生活随笔為你收集整理的redshift教程_分析和可视化Amazon Redshift数据—教程的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。