azure服務器_如何使用Azure Functions和SendGrid構建無服務器報表服務器

azure服務器

It’s 2018 and I just wrote a title that contains the words “Serverless server”. Life has no meaning.

那是2018年，我剛剛寫了一個標題，其中包含“無服務器服務器”一詞。生活沒有意義。

Despite that utterly contradictory headline, in this article we’re going to explore a pretty nifty way to exploit SendGrid’s template functionality using Timer Triggers in Azure Functions to send out scheduled tabular reports. We are doing this because that’s what everyone wants in their inbox. A report. With numbers in it. And preferably some acronyms.

盡管標題完全矛盾，但在本文中，我們將探索一種非常漂亮的方法，即使用Azure Functions中的計時器觸發器來利用SendGrid的模板功能來發送計劃的表格報表。我們這樣做是因為每個人都希望在收件箱中。一份報告。里面有數字。并且最好是一些縮寫。

庫存SKU報告 (The Inventory SKU Report)

First, let’s straw-man this project with a contrived application that looks sufficiently boring enough to warrant a report. I have just the thing. A site where we can adjust inventory levels. The word “inventory” is just begging for a report.

首先，讓我們用一個人為設計的應用程序來研究這個項目，它看起來足夠無聊，足以保證報告。我有東西。我們可以調整庫存水平的站點。 “庫存”一詞只是乞求一份報告。

This application allows you to adjust the inventory quantity (last column). Let’s say that an executive somewhere has requested that we email them a report every night that contains a list of every SKU altered in the last 24 hours. Because of course, they would ask for that. In fact, I could swear I’ve built this report in real life in a past job. Or there’s a glitch in the matrix. Either way, we’re doing this.

此應用程序允許您調整庫存數量(最后一列)。假設某位高管要求我們每晚向他們發送一份報告，其中包含過去24小時內更改的每個SKU的列表。當然，他們會要求這樣做。實際上，我可以保證我在過去的工作中已經在現實生活中構建了此報告。或矩陣中出現故障。無論哪種方式，我們都在這樣做。

Here is what we’re going to be building…

這就是我們將要建造的……

Normally the way you would build this is with some sort of report server. Something like SQL Server Reporting Services or Business Objects or whatever other report servers are out there. Honestly, I don’t want to know. But if you don’t have a report server, this gets kind of tedious.

通常，構建此報表的方式是使用某種報表服務器。諸如SQL Server報表服務或業務對象之類的東西或其他任何報表服務器都在那兒。老實說，我不想知道。但是，如果您沒有報表服務器，這將變得很乏味。

Let’s go over what you have to do to make this happen…

讓我們回顧一下實現此目標所需要做的工作……

Run a job on some sort of timer (cron job)
在某種計時器上運行作業(定時作業)
Query a database
查詢數據庫
Iterate over records and format them for output to the screen
遍歷記錄并將其格式化以輸出到屏幕
Email said report
電子郵件說報告
Update your resume and contact recruiters
更新您的簡歷并聯系招聘人員

This is the kind of thing that nobody wants to do. But I think this project can be a lot of fun, and we can use some interesting technology to pull it off. Starting with Serverless.

這是沒人愿意做的事情。但是我認為這個項目可能會很有趣，我們可以使用一些有趣的技術來實現它。從無服務器開始。

無服務器計時器功能 (Serverless timer functions)

Serverless is a really good use case for one-off requests like this. In this case, we can use Azure Functions to create a Timer Trigger function.

對于這樣的一次性請求，無服務器是一個非常好的用例。在這種情況下，我們可以使用Azure Functions創建一個Timer Trigger函數。

To do that, I’m going to use the Azure Functions extension for VS Code. I’m going to use it for everything in fact. Why? Because I don’t know you, but I do know it’s highly likely that you are using VS Code. VS Code is great because it’s like a movie that all developer’s can universally agree is completely awesome. Sort of the opposite of “Children of Men”. That movie was terrible and you know it.

為此，我將對VS Code使用Azure Functions擴展。實際上，我將用它來做所有事情。為什么？因為我不認識您，但我確實知道您很有可能正在使用VS Code。 VS Code很棒，因為它就像一部電影，所有開發人員都可以普遍認為這是一部很棒的電影。有點像“男人的孩子”。那部電影太可怕了，你知道的。

Make sure you install the Azure Functions extension.

確保安裝Azure Functions擴展。

Azure Functions - Visual Studio MarketplaceExtension for Visual Studio Code - An Azure Functions extension for Visual Studio Code.marketplace.visualstudio.com

Azure功能 -Visual Studio代碼的 Visual Studio市場 擴展-Visual Studio代碼的Azure函數擴展。 marketplace.visualstudio.com

Now create a new Function App from within VS Code.

現在，從VS Code中創建一個新的Function App。

Then create a new Timer Trigger function. Timer Trigger functions are scheduled using standard Cron Expressions. You have likely not ever seen before because I had not seen one until a few months ago. And I’ve been in this industry for a LONG time. I am old, father William.

然后創建一個新的計時器觸發功能。計時器觸發功能是使用標準Cron表達式安排的。您可能以前從未見過，因為幾個月前我才見過。我在這個行業工作了很長時間。我老了，父親威廉。

Cron expressions look kind of scary cause they have asterisks in them. In the case below, I’m saying that when minutes is 0 and seconds is 0 and hours is evenly divisible by 24, fire the function. This would be midnight.

Cron表達式看起來有點嚇人，因為它們中帶有星號。在以下情況下，我要說的是，當分鐘為0且秒為0且小時可以被24整除時，觸發該函數。這將是午夜。

Now we can run this locally (F5). We’ll see in the embedded terminal the schedule on which our Function will be called; the next 5 occurrences.

現在我們可以在本地運行它(F5)。我們將在嵌入式終端中看到將調用我們的Function的時間表；接下來的5次。

It feels good, man.

感覺很好，伙計。

OK, now we need to get some data. I’m not going to drag you into the specifics of me querying SQL Server from this function because that’s not what this article is about, but here’s the code anyway.

好的，現在我們需要獲取一些數據。我不會把您拖到從該函數查詢SQL Server的細節中，因為這與本文無關，但無論如何這里都是代碼。

const { Connection, Request } = require('tedious');const options = {weekday: 'long',year: 'numeric',month: 'long',day: 'numeric'
};const config = {userName: process.env.SQL_USERNAME,password: process.env.SQL_PASSWORD,server: process.env.SQL_SERVER,options: {encrypt: true,database: process.env.SQL_DATABASE}
};module.exports = function(context, myTimer) {getChangedSkus().then(data => {if (data.length > 0) {sendEmail(context, data);} else {context.done();}}).catch(err => {context.log(`ERROR: ${err}`);});
};/*** Executes a query against the database for SKU's changed in the last 24 hours* @returns {Promise} Promise object contains result of query*/
function getChangedSkus() {return new Promise((resolve, reject) => {const connection = new Connection(config);const query = `SELECT Sku, Quantity, CONVERT(varchar, Modified, 0) as ModifiedFROM InventoryWHERE Modified >= dateadd(day, -1, getdate())`;connection.on('connect', err => {if (err) reject(err);let request = new Request(query, err => {if (err) {reject(err);}});const results = [];request.on('row', columns => {let result = {};columns.forEach(column => {result[column.metadata.colName] = column.value;});results.push(result);});request.on('doneProc', (rowCount, more) => {resolve(results);});connection.execSql(request);});});
}

I’m connecting to the database, doing a simple query and….wait a minute…did not I say I wasn’t going to get into specifics? You had me there for a minute, but I’m onto your game!

我正在連接到數據庫，進行簡單的查詢，然后……等一下……不是我說我不打算講細節嗎？你在那兒呆了我一分鐘，但是我正在玩你的游戲！

So this pulls in data and we get it in a JavaScript object that we can pass as JSON. If we were to JSON.stringify this, we will see the data set that we need to send in the report.

因此，這會提取數據，并將其獲取到我們可以作為JSON傳遞JavaScript對象中。如果要使用JSON.stringify ，我們將在報告中看到需要發送的數據集。

[{ "Sku": "1", "Quantity": 65, "Modified": "Nov  6 2018 10:14PM" },{ "Sku": "10", "Quantity": 89, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "11", "Quantity": 39, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "12", "Quantity": 2, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "13", "Quantity": 75, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "14", "Quantity": 85, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "15", "Quantity": 58, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "16", "Quantity": 2, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "17", "Quantity": 48, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "18", "Quantity": 68, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "19", "Quantity": 67, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "2", "Quantity": 5, "Modified": "Nov  6 2018 11:18PM" },{ "Sku": "20", "Quantity": 37, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "21", "Quantity": 54, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "22", "Quantity": 21, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "23", "Quantity": 46, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "24", "Quantity": 55, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "25", "Quantity": 21, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "26", "Quantity": 42, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "27", "Quantity": 65, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "28", "Quantity": 74, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "29", "Quantity": 33, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "3", "Quantity": 51, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "4", "Quantity": 96, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "5", "Quantity": 27, "Modified": "Nov  6 2018 11:18PM" },{ "Sku": "6", "Quantity": 13, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "7", "Quantity": 54, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "8", "Quantity": 89, "Modified": "Nov  2 2018  8:18PM" },{ "Sku": "9", "Quantity": 56, "Modified": "Nov  2 2018  8:18PM" }
]

OK! We’ve got data, now we just need to make it pretty and email it to someone we don’t like. How are we going to do that? With SendGrid!

好！我們有數據，現在只需要使其漂亮，然后通過電子郵件將其發送給我們不喜歡的人即可。我們該怎么做？使用SendGrid！

SendGrid設置 (SendGrid setup)

SendGrid is a nifty service with a really nice dashboard. You will like it. Or you won’t. Either way, you have to use it to get through this blog post.

SendGrid是一個很棒的服務，具有非常好的儀表板。你會喜歡它。否則你不會。無論哪種方式，您都必須使用它來瀏覽此博客文章。

You can create a free account if you don’t already have one. That’s plenty for what we’re doing here today.

如果您還沒有免費帳戶，則可以創建一個免費帳戶。對于我們今天在這里所做的事情來說，這已經足夠了。

Once you create a report, SendGrid is going to drop you into your “dashboard”. From this dashboard, you need to create a new API Application and get the key.

創建報告后，SendGrid將使您進入“儀表板”。從此儀表板，您需要創建一個新的API應用程序并獲取密鑰。

Make sure you copy your API key when it gives it to you. You can’t ever get back to it and you’ll have to do this all over again. Let’s face it: it was kinda boring the first time around.

確保將API密鑰提供給您時將其復制。您再也無法恢復原狀，您將不得不重新做一遍。讓我們面對現實：這是第一次很無聊。

Copy that key into your Azure Functions project. Put it in the local.settings.json file so you can access it as a Node.js environment variable later.

將該密鑰復制到您的Azure Functions項目中。將其放在local.settings.json文件中，以便以后可以將其作為Node.js環境變量進行訪問。

{"IsEncrypted": false,"Values": {"AzureWebJobsStorage": "DefaultEndpointsProtocol=https;AccountName=reporttimerstorage;AccountKey=OJVYCHI0GhtIm5XZdsDzGZFraJD/v/rfPwMSu4B72Kf5/O7oCrOQKNAFkQ==","FUNCTIONS_WORKER_RUNTIME": "node","SENDGRID_API_KEY": "SG.rlpDOy3EQNOTChnzpa1COPYg.G4MYlEYhwHk0RyvuGcY_xKEYbhQoFTtPB9A9-5ZaYQ"}
}

Now we are going to create a template in SendGrid. That’s what we will use to design our report. SendGrid has something called “Transactional Templates”. I have no idea why they are called that, but we are going to be needing one.

現在，我們將在SendGrid中創建一個模板。這就是我們將用來設計報告的內容。 SendGrid有一個稱為“事務模板”的東西。我不知道為什么要這樣稱呼他們，但我們將需要一個。

Once you create a new one, you have to create a new “version”. I had a hilariously hard time figuring this out. But then again, my brain is tad on the smallish side of little.

創建新版本后，您必須創建一個新的“版本”。我很難解決這個問題。但是話又說回來，我的大腦在小小的一面。

Choose to design your template with the Code Editor. You don’t need no freakin’ Designer Editor!

選擇使用代碼編輯器設計模板。您不需要任何freakin'Designer編輯器！

SendGrid support handlebars, which is a template syntax that’s so easy, even I can do it. In the Code Editor, you can paste the JSON data into the “Test Data” tab…

SendGrid支持把手，這是一個模板語法，非常簡單，即使我也可以做到。在代碼編輯器中，您可以將JSON數據粘貼到“測試數據”標簽中…

Now iterate over the data using its key name from the JSON…

現在，使用來自JSON的鍵名遍歷數據…

It’s BEAUTIFUL! I’m crying. Ship it.

真漂亮！我在哭裝運它。

ALRIGHT. Fine. We’ll make it a little nicer on the old eyeballs. Here is a style that I shamelessly ripped off of the gorgeous Bulma CSS framework.

好的。精細。我們將在舊的眼球上使它更好一點。這是我從華麗的Bulma CSS框架中毫不客氣地撕下的樣式。

<style>table {border-collapse: collapse;border-spacing: 0;background-color: white;color: #363636;}.table td,.table th {border: 1px solid #dbdbdb;border-width: 0 0 1px;padding: 0.5em 0.75em;vertical-align: top;}.table th {color: #363636;text-align: left;}.table thead td,.table thead th {border-width: 0 0 2px;color: #363636;}.table tbody tr:last-child td,.table tbody tr:last-child th {border-bottom-width: 0;}.table.is-bordered td,.table.is-bordered th {border-width: 1px;}.table.is-bordered tr:last-child td,.table.is-bordered tr:last-child th {border-bottom-width: 1px;}.table.is-fullwidth {width: 100%;}.container {margin: 0 auto;position: relative;max-width: 960px;padding-top: 20px;font-family: helvetica, sans-serif;}
</style><div class="container"><h1>Modified SKUs</h1><p>The following SKU's were modified in the last 24 hours</p><table class="table is-fullwidth"><thead><tr><th>Sku</th><th>Quantity</th><th>Last Modified</th></tr></thead><tbody>{{#each Skus}}<tr><td>{{Sku}}</td><td>{{Quantity}}</td><td>{{Modified}}</td></tr>{{/each}}</tbody></table>
</div>

It’s ok at this point for you to be audibly impressed.

此時，可以給您留下深刻的印象。

Now you might have noticed that the Subject of the email is missing. How do we fill that in? Well, after another embarrassing period of failure followed by introspection, I figured out that it’s behind the “Settings” icon on the left. You just have to pass a value in your JSON for “Subject”.

現在您可能已經注意到電子郵件的主題丟失。我們如何填寫呢？好吧，在經歷了令人尷尬的失敗和自省之后，我發現它位于左側的“設置”圖標后面。您只需要在JSON中為“主題”傳遞一個值。

Now we need to get the template ID and add it to our Azure Functions project. Save this template and select the ID from the main template screen.

現在，我們需要獲取模板ID，并將其添加到我們的Azure Functions項目中。保存此模板，然后從主模板屏幕中選擇ID。

Drop it in the trusty local.settings.json file right underneath your SendGrid API key.

將其放置在SendGrid API密鑰下方的可信任的local.settings.json文件中。

{"IsEncrypted": false,"Values": {"AzureWebJobsStorage": "DefaultEndpointsProtocol=https;AccountName=reporttimerstorage;AccountKey=OJVYCHI0GhtIm5XZdsDzGZFraJD/v/rfPwMSu4B72Kf5/O7oCrOQKNAFkQ==","FUNCTIONS_WORKER_RUNTIME": "node","SENDGRID_API_KEY": "SG.rlpDOy3EQNOTChnzpa1COPYg.G4MYlEYhwHk0RyvuGcY_xKEYbhQoFTtPB9A9-5ZaYQ""SENDGRID_TEMPLATE_ID": "d-3e33c1453cf7457fb06e6d30519bd422"}
}

Now we are ready to pass our data from our Azure Function to SendGrid and send out this incredible work of business art.

現在，我們準備將數據從Azure功能傳遞到SendGrid，并發送這種令人難以置信的商業藝術品。

Azure函數的SendGrid綁定 (SendGrid bindings for Azure Functions)

Azure Functions provides a binding for SendGrid. If you create a function through the Azure Portal, it will create this binding for you when you select the “SendGrid” template. If you are doing it locally like I am, you have to add it yourself.

Azure Functions為SendGrid提供了綁定。如果通過Azure門戶創建函數，則在選擇“ SendGrid”模板時將為您創建此綁定。如果您像我一樣在本地進行操作，則必須自己添加。

First you need to open the function.json file for the CreateReport function and add in the SendGrid binding.

首先，您需要打開CreateReport函數的function.json文件，并添加SendGrid綁定。

{"type": "sendGrid","name": "message","apiKey": "SENDGRID_API_KEY","to": "youremail@company.com","from": "hahabusiness@businesstime.com","direction": "out"
}

The SendGrid binding comes as an extension for Azure Functions. Run the following command in the terminal to install it.

SendGrid綁定是Azure功能的擴展。在終端中運行以下命令進行安裝。

Microsoft.Azure.WebJobs.Extensions.SendGrid -Version 3.0.0

When you run this command, VS Code will ask you to restore some dependencies. You can click restore. Nothing bad will happen…OR WILL IT?!

當您運行此命令時，VS Code將要求您還原一些依賴項。您可以單擊還原。不會發生任何壞事...或者會嗎？

One other thing you need to do is tweak your extensions.csproj file to reference the latest SendGrid library. This is required to use dynamic templates.

您需要做的另一件事是調整extensions.csproj文件以引用最新的SendGrid庫。這是使用動態模板所必需的。

<PackageReference Include="Sendgrid" Version="9.10.0" />

When you add that, VS Code will prompt you to restore again and yes, you definitely need to do it this time. VS Code needs to build these binaries and the restore does that.

添加后，VS Code會提示您再次還原，是的，您這次肯定需要這樣做。 VS Code需要構建這些二進制文件，然后還原即可完成。

OK! Now we’re ready to send an email via our SendGrid template. Here is the code to do it. It’s depressingly simple. I know after all this you were hoping for enough code to choke a cat (what? you’ve never heard that metaphor before?), but this is all it takes.

好！現在，我們準備通過我們的SendGrid模板發送電子郵件。這是執行此操作的代碼。令人沮喪的簡單。我知道所有這些之后，您希望有足夠的代碼來扼殺貓(這是什么？您以前從未聽說過這種隱喻嗎？)，但這就是全部。

function sendEmail(context, data) {context.done(null, {message: {/* you can override the to/from settings from function.json here if you would liketo: 'someone@someplace.com',from: 'someone@anotherplace.com'*/personalizations: [{dynamic_template_data: {Subject: `Tailwind SKU Report For ${new Date().toLocaleDateString('en-US',options)}`,Skus: data}}],template_id: process.env.SENDGRID_TEMPLATE_ID}});
}

The items of note are me passing in a Subject as part of the JSON. As well as the fact that you can override to/from addresses specified in the function.json file here.

注意事項是我傳入了Subject作為JSON的一部分。以及您可以覆蓋此處/來自function.json文件中指定的地址的事實。

Now you can run your function and wait 24 hours to test it!

現在，您可以運行您的函數并等待24小時進行測試！

No but seriously — how do you manually test a Timer Trigger without constantly modifying the damn Cron Job?

不，但是很認真-您如何在不不斷修改該死的Cron Job的情況下手動測試計時器觸發器？

I’ll show you how I do it and then you can figure out a better way.

我將向您展示如何做到這一點，然后您可以找出一種更好的方法。

使用http觸發器測試計時器觸發器 (Testing timer triggers with http triggers)

I create an Http Trigger in the same project and call it “RunCreateReport”. In that function, I just import and call the timer function.

我在同一項目中創建一個Http觸發器，并將其稱為“ RunCreateReport”。在該函數中，我只是導入并調用timer函數。

const index = require('../CreateReport/index');module.exports = function(context, req) {// This is a tester function that manually executes the CreateReport timer functionindex(context);
};

The only drawback to this is that you have to repeat your SendGrid binding settings from function.json in the “CreateReport” over in the “RunCreateReport” function.json. But other than that, this works just fine. Now you can run this thing, fire up a browser and hit the URL which will call the timer function immediately. You can test without having to touch that icky old Cron expression.

這樣做的唯一缺點是，必須在“ RunCreateReport” function.json的“ CreateReport”中重復執行function.json中的SendGrid綁定設置。除此之外，這還行得通。現在您可以運行該程序，啟動瀏覽器并單擊URL，該URL將立即調用timer函數。您可以進行測試，而無需觸碰那個令人討厭的Cron舊表達式。

哈哈業務 (HAHA business)

Now go check your email and bask in the glory of the report. Note that you don’t have to own an email address to send from SendGrid. You can literally send from any address. Seriously. Go ahead and try. JUST THINK OF WHAT YOU CAN DO WITH THIS POWER.

現在，請檢查您的電子郵件，并仔細閱讀報告的內容。請注意，您不必擁有要從SendGrid發送的電子郵件地址。您可以從任何地址直接發送。說真的繼續嘗試。只需考慮一下您可以使用此功能做什么。

Here’s what my inbox looks like. Heads up, it does go to junk. Probably because I don’t own the sender email address.

這是我的收件箱的樣子。抬起頭，它確實會變成垃圾。可能是因為我沒有發件人的電子郵件地址。

WHAT? There’s a “Business Resilience Conference”? OMG so much business. I bet those people get a LOT of reports.

什么？有“業務彈性會議”嗎？ OMG生意這么好。我敢打賭那些人會得到很多報道。

You can get this project from Github.

您可以從Github獲得該項目。

burkeholland/serverless-sendgrid-reportContribute to burkeholland/serverless-sendgrid-report development by creating an account on GitHub.github.com

burkeholland / serverless- sendgrid-report通過在GitHub上創建一個帳戶來為burkeholland / serverless-sendgrid-report開發做出貢獻。 github.com

Here are a few other Azure Functions resources to keep you busy.

這是其他一些Azure Functions資源，可讓您保持忙碌。