DEV Community: LaBrina Loving

Set Azure PlayFab Title Data from Google Sheets with No Code

LaBrina Loving — Tue, 18 Jan 2022 04:20:54 +0000

So recently I've seen a lot of people in the Azure PlayFab community ask how they could create a workflow that takes data that is stored online in Github, Google Sheets, or other files and publish to PlayFab. For example, your game designer might want to tune the experience that is required to reach a level. The game designer is using Google Sheets to store this information. You could go straight into the Game Manager to update this, but it might be better to do this in a more automated way with clear separation of roles on the team. One of my favorite ways to automate tasks like these is with Azure Logic Apps.

What is Azure Logic Apps

Azure Logic Apps provides a serverless engine to build visual automated workflows to integrate apps and move data between applications, usually with little transformation. You build workflows using a visual designer. This means the following tasks can be done with no code:

Sending a weekly news summary or leaderboard updates to all players via Discord, email, or other channels.
Automatically notifying team when a player is removed from game due to bad behavior or other moderation issues.
Exporting PlayStream and other Telemetry events into Excel or Google Sheets for analysis
Monitoring tweets or other social media activity of your game, analyze the sentiment, and create alerts or tasks for the team to review.

How Azure Logic Apps works

At it's core, Azure Logic Apps is just a series of steps, or workflow, to define a process. Each workflow is started by a trigger. This could be an event, such as when a someone Tweets about your game, or a scheduled time that occurs daily/weekly, or it could even be run on-demand using an HTTP request.

Once an event is triggered, the workflow begins to run the tasks that you've created. You can pick from the over 400+ connectors as well as using the built-in actions to achieve powerful integrations for your game. One of the built-in connectors is Azure Functions, so you could combine your CloudScripts with other actions in a visual manner.

Prerequisites

Azure Subscription - if you don't have one you can sign up for a free account
PlayFab account - you can sign up here

Let's Do This

Okay, enough talk, let's go ahead and get started with our first workflow. As mentioned earlier, we want to create a workflow that updates PlayFab Title Data from a Google Sheet that the Game Designer manages.

Because PlayFab Title Data is stored as key/value pairs, and our data is stored in Google Sheets, we'll need to convert it to JSON and then send to PlayFab.

I log in to the Azure Portal, search for Logic Apps and create.

Fill in the required information:
Resource Group - resource group is a logical grouping of Azure Services together so that they can managed together. Just enter a name that makes sense.
Type - we're going to select Consumption for now as it makes the most sense for now. For more on pricing options for Azure Logic Apps, check here.
Name - Enter a name that makes sense
Region - Pick the region that makes the most sense based on your location.
Enable Log Analytics - Because I'm just doing a demo, I won't turn on Log Analytics, but definitely when running your Logic App in Test and Production, you should enable Log Analytics.

Fill in the information and Review and Create to complete creating the Logic App. When you go to the resource, you'll see the visual designer. You'll be prompted with a bunch of triggers and templates.

In our example we will just check our Google Sheet nightly for updates to the level design. To do this we'll select the Recurrence Trigger. When the workflow opens, select the Recurrence trigger and change recurrence to 1 time per day and add the At these Hours and At these Minutes parameters as shown below.

First thing I'll need to do in my workflow is connect to Google Sheets. Click the plus sign to add a new action, search for Google Sheets and select Get Rows - Google Sheets. Sign in to your Google account to connect and you then will be able to select your Google Sheet.

Next we want to take the items in the Google Sheet and select the values into an array. For this, we will use the built-in Select Data Operation. Add another action and search for Select. In the From field, I will select Records Value. For mapping, I will create two fields Level and XP and map them to the values in my Google Sheet as shown below:

Now that we have the data, we can create a JSON object that we will pass into our PlayFab Title API like this. To do this, we will use the Compose action, which creates a single JSON output based on several inputs. We add another action search for Compose. We build the input by pasting the following in the inputs field:
{ "Key": "CharacterLevelRamp", "Value": "" }
Then put your cursor in the Value and select Output from the action above.

For the last step, we will call the PlayFab Set Title Data API with our data. To do this, add the HTTP action and add the following:
Method - Post
URL - https://[yourTitleID].playfabapi.com/Admin/SetTitleData
Headers - X-SecretKey - Your Developer Key
Body - Select Output from previous step above

...and that's it! Now let's test it out. Make sure you save your workflow. Click Run Trigger to execute your Workflow. You will see trigger running in Runs History. If all goes well, we will see all green checkboxes. Go into PlayFab --> Title Settings --> Engage --> Content --> Edit Title Data and you should see CharacterLevelRamp set.

Migrating SharePoint Files to Azure Data Lakes Storage

LaBrina Loving — Fri, 29 Jan 2021 20:15:59 +0000

So I recently had a customer that had several terrabytes of data in SharePoint that wanted to move to Azure Data Lakes Storage in order to start doing transformations for some Machine Learning experiments. Typically when migrating data to ADLS I would use something like Azure Data Factory Copy Activity, however SharePoint is not currently supported. However we can copy over these items easily using the Mover Migration Tool. The Mover Migration tool allows you to migrate docs from users or just SharePoint document libraries to Azure Data Lakes Storage. For the purposes of this, we are going to migrate a SharePoint Document library to ADLS. When copy over the documents, it will maintain the folder structure as well.

Prerequisites

User must be a SharePoint Admin in your tenant.
User must have Global Administrator, Application Administrator, or a Cloud Application role on your Azure AD Tenant. This will be needed to Grant consent to the Mover.io enterprise application
You have already provisioned your Azure Data Lakes Storage and created a container and folder(or folders) to migrate data from SharePoint.
The Mover Migration Tool uses a CSV mapping file to map SharePoint Sites to ADLS. Create a CSV file with the headers Source Path and Destination Path. Then add lines for each source SharePoint site and Destination ADLS folder like so: https://labrandoncloud.sharepoint.com/sites/TeamSite/Shared%20Documents, sharepointtarget/sharepointdata. In this example https://labrandoncloud.sharepoint.com/sites/TeamSite/Shared%20Documents is my SharePoint Source document library and sharepointtarget/sharepointdata is my container/folder name in the destination ADLS. For more information on creating CSV files, see here.

Steps

Go to https://mover.io/ and login with an account that is the SharePoint Admin and also AAD Admin that can create Enterprise Applications.
Once you login, a new Enterprise Application is created. You can verify by going to your AAD tenant and going to Enterprise Applications. You should see Office 365 Mover listed as an Enterprise Application.
In Enterprise Applications, click on the Office 365 Mover application, and then select Permissions on the left. Click Grant admin consent for tenant-name.
Go back to Mover, click Authorize New Connector on Source side. Select Office 365(OneDrive/SharePoint Admin) from list and click Authorize button.
Now on destination side, click Authorize New Connector and select Azure Blob Storage. Enter name of Blob storage and key.
Once the Source and Destination has been configured, you can move onto Step 3: Start Transferring. Click Continue Migration.
The Mover Migration tool migrates data using mapping CSV files as discussed in Prerequisite sections. Click on Add Users button and upload CSV file you created. You should now see mapping uploaded.
If you are migrating a lot of data, I would suggest doing a Scan first. This will allow you to see how many files/folders will be migrated.
Once you are ready, you can click Start Migrating User to copy data from SharePoint to ADLS. Select Right Now, agree to terms, and click Start Migration. Once complete, it will show a report of files/folders copied.

Et Voilá c'est fini

Serverless Big Data Pipelines - Exploring NFL Football Data with Azure Databricks

LaBrina Loving — Sat, 12 Sep 2020 20:31:38 +0000

This article is part of #AzureBacktoSchool. You'll find other helpful articles and videos in this Azure content collection. New articles are published every day from community members and cloud advocates in the month of September. Thank you Dwayne Natwick for organizing this awesome idea!

So along with going back to school, American Football returned this week and I'm ready for some Football!!! I've always been very fascinated with how teams and sports analyst use historical data to predict player performance and predict who will win the game and other player statistics. The concepts and approach here is no different than how you might use Big Data in Sales Forecasting, Predictive Repair for machinery, Inventory Demand scenarios.

This blog post will show how to start building a big data analytics platform using serverless technologies. I will show how to move data from a source to Azure Data Lakes using Azure Data Factory and then do some exploration and analysis using Azure Databricks. This will be the first in many posts I will do on Serverless Big Data Pipelines using football datasets.

Big Data Architectures

When you think about a big data analytics platform, there are typically these components:

Data sources. All big data solutions start with one or more data sources. For my solution, I've identified several sources listed here. For the purposes of this blog, I'm going to play around with play by play data found here.
Data storage. Data for batch processing operations is typically stored in a distributed file store that can hold high volumes of large files in various formats. This kind of store is often called a data lake. A data lake provides a centralized store for data engineers and data scientists to explore data in it's raw state. I will be using Azure Data Lake Storage Gen 2. Azure Data Lake Storage provides fast, secure, scalable cloud storage along with hierarchical namespaces and a file access control using a combination of role based access (RBAC) and POSIX-compliant Access Control Lists (ACLs).
Orchestration. Most big data solutions consist of repeated data processing operations, encapsulated in workflows, that transform source data, move data between multiple sources and sinks, load the processed data into an analytical data store, or push the results straight to a report or dashboard. In our solution, we will be moving files from various sources exploring, cleansing, experimenting, and aggregating. For this, we will use Azure Data Factory.
Batch Processing. Big data solutions often use long-running batch jobs to filter, aggregate, and otherwise prepare the data for analysis. We'll use Azure Databricks is an Apache Spark-based analytics platform.
Analytical data store. Many big data solutions prepare data for analysis and then serve the processed data in a structured format that can be queried using analytical tools. I won't yet cover these in this blog post.
Analysis and reporting. The goal of most big data solutions is to provide insights into the data through analysis and reporting. In this you might use tools like Power BI or Azure Synapse. I also won't yet cover these in this blog post.

Getting Started

Ok, enough theory, let's actually build something!!! To get started, let's provision the following services in our Azure Portal:

Once you've got these services setup as shown below.

You'll want to go ahead and create a container in your ADLS.
Make sure that our Azure Data Factory can access our ADLS. You'll do this by giving the Data Factory 'Storage Blob Data Contributor' rights to our ADLS using it's Managed Service Identity. Managed Service Identity is a way to give an identity to your Azure resources in Azure AD and handles cleaning up this identity automatically when the resource is deleted. A managed identity is automatically created when you create an ADF resource through the Azure Portal.

To do this, go to the ADLS and select Access Control(IAM). Click Add role assignment. Select Storage Blob Data Contributor and select the name of the Azure Data Factory resource you created:

Copying data to ADLS

Now that the resources are setup in Azure, let's create a pipeline to copy over the files you need into ADLS. Go into Azure Data Factory, click Author and Monitor.

You'll see an authoring canvas with a lot of quick start buttons to get you started. Click on the Copy Data to launch the wizard.

Enter name for your copy pipeline and click 'Next'
Next up, you will setup our data source. In order to setup a data source, you must create a Dataset and a Linked Service. A dataset is a named view of data that simply points or references the data you want to use. Linked services are much like connection strings, which define the connection information needed for Data Factory to connect to external resources. So in setting up your data source, the dataset is the "what" and the linked service is the "how". Azure Data Factory supports over 90 data connectors.

For this blog, I will start with the 2019 play-by-play data located here. Because we are using csv files located in Github, I will use the HTTP connector. To create your Http Connector Data Source, select File, and Add new Connection to create your HTTP Linked Service.
Give your Linked Service a name, and set the Base URL to 'https://raw.githubusercontent.com/ryurko/nflscrapR-data/master/'. I'm going to set this up with a Base URL because I will likely be copying several files over from this source and it will be easier to manage as my linked service. Set Authentication type to 'Annonymous' and click Create.

You should see your Linked Service created, click next to setup dataset.
To setup our HTTP dataset, enter the relative url as 'play_by_play_data/regular_season/reg_pbp_2019.csv' and click Next. You should see a preview of the file like this:
Click next.
To setup ADLS as our destination store, you will again need to create a Linked Service and Dataset. Since you are saving this file to ADLS, select category Azure and click 'Create new connection'. Select Azure Data Lake Storage Gen2 and click Continue.
Give your linked service a name, change authentication to Managed Identity, select the ADLS storage you created.
Enter the output folder and destination file name.
Click through to summary to show our copy pipeline setup.
Finally deploy and run the pipeline.
To verify that our file has been properly copied over, you can open Storage Explorer, go to our Storage container and confirm file is there.

Data Exploration

So now that I have some files in my Data Lake, let's start exploring. Typically you will want to explore the data, start building out data definitions, removing any unnecessary columns, and cleansing. To start exploration, I will use Azure Databricks.

There are three ways of accessing Azure Data Lake Storage Gen2:

Mount an Azure Data Lake Storage Gen2 filesystem to DBFS using a service principal and OAuth 2.0.
Use a service principal directly.
Use the Azure Data Lake Storage Gen2 storage account access key directly.

For production systems, I would highly recommend going with option 1., but since we are just starting out, let's pick option 3.

To get started, launch the Azure Databricks resource you created above. Create a blank notebook. In the first cell, you will connect to our ADLS context:

spark.conf.set(
"fs.azure.account.key.<storage account name>.dfs.core.windows.net",
  "<storage account key>"
)

Now that we've connected to our storage, we'll want to test out our connection by listing all the files in the directory. We will use the Filesystem utilities in Databricks for this:

dbutils.fs.ls("abfss://<container name>@<storage account name>.dfs.core.windows.net")

When you Run all, you will see the list command shows our .csv files copied over.

Now that the files are mounted, load them into a dataframe. A dataframe is a just a table similar to SQL table or even an Excel datasheet that you will perform operations like selecting, filtering, grouping, and joining. The only difference is that it exists in memory only. To do this write the following code in our cell:

#set the data lake file location:
file_location = "abfss://<container name>@<storage name>.dfs.core.windows.net/*.csv"

#read in the data to dataframe df
df = spark.read.format("csv").option("inferSchema", "true").option("header", "true").option("delimiter",",").load(file_location)

#display the dataframe
display(df)

Notice I've done a couple of things. I've set the file location to *.csv. This will actually read all of our play-by-play csv files instead of just one. I've also set "inferSchema" to true which and "header" to true. Run all again, and you should see all of the rows in the file loaded into the dataframe:

Now at long last, let's start doing some data exploration with some queries. To start, let's say that I want to see total passing yards by team and player. In a new cell, I'll put the following code:

%sql
SELECT
  posteam,
  passer_player_name,
  sum (yards_gained)
FROM
  playbyplay_view
where
  play_type = "pass" and yards_gained > 0
group by
  posteam,
  passer_player_name

I used the magic %sql command so I can now write queries against my dataframe using normal SQL statements. When I run this command, I see the following

Great!!! I'm now setup to do more exploration of this data and figure out key data that I want to further analyze and aggregate into permanent data store.

Power to the Enterprise – Building Bots with Power Virtual Agents

LaBrina Loving — Wed, 08 Jul 2020 19:36:41 +0000

#PowerfulDevs Conference

RSVP HERE BY JULY 15:
https://aka.ms/powerfuldevsconf

The Powerful DEVs Conference is the first virtual conference of its kind. We will showcase how developers can leverage the Power Platform to build applications faster and with far less effort. Connect with industry-recognized ProDev influencers, Microsoft Cloud Advocates, trusted and diverse community leaders, and members of the Power Platform Team.

BOOKMARK THIS FOR RESOURCES AND DISCUSSION WITH SPEAKERS

#powerfuldevs Conference: Join us on July 15th Online!

JennyMEvents for Microsoft Azure ・ Jul 10 '20

#powerfuldevs #powerplatform #prodev #programming

Revisit this page during the event to engage in live (and post-event) discussions on those topics with both speakers and community. The speakers will be here for a live Q&A for at least 30 minutes immediately after their session concludes. After the event, come back to find additional slides, videos, and resources for this session.

About This Session:

July 15, 2020: 12:00 PDT - 12:25 PDT

Power Virtual Agents empowers teams to easily create powerful bots using a guided, no-code graphical interface. It also allows users to add custom skills built with pro-code tools. In this session, I will walk though how both low-code and pro-code developers can work together to build bots. I will discuss the software development lifecycle for bots with Power Virtual Agent and key points to consider.

About the Speaker:

LaBrina Loving is a Senior Engineer @ Microsoft focusing on Azure for ISV's and Startups. Follow @chixcancode on Twitter.

Additional Resources: aka.ms/powerbotframework