teste de unidade em U-SQL que cria tabelas de nós e arestas e insere dados nessas tabelas em um GraphDB:

[marcialwushu]

using Microsoft.VisualStudio.TestTools.UnitTesting;
using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using Microsoft.Analytics.Samples.Sql;

namespace MyUsqlTests
{
    [TestClass]
    public class GraphDbTests
    {
        [TestMethod]
        public void InsertNodeAndEdgeData()
        {
            // Set up U-SQL environment
            TestUsqlEnvironment environment = new TestUsqlEnvironment();
            environment.ConnectToDatabase("MyGraphDB");

            // Create node table
            string createNodeTableScript = @"
                CREATE TABLE IF NOT EXISTS Nodes (
                    Id int,
                    Name string,
                    Type string
                );
            ";
            environment.ExecuteScript(createNodeTableScript);

            // Create edge table
            string createEdgeTableScript = @"
                CREATE TABLE IF NOT EXISTS Edges (
                    SourceId int,
                    TargetId int,
                    Type string
                );
            ";
            environment.ExecuteScript(createEdgeTableScript);

            // Insert node data
            string insertNodeDataScript = @"
                INSERT INTO Nodes (Id, Name, Type)
                VALUES
                    (1, 'John', 'Person'),
                    (2, 'Jane', 'Person'),
                    (3, 'Acme Inc.', 'Organization');
            ";
            environment.ExecuteScript(insertNodeDataScript);

            // Insert edge data
            string insertEdgeDataScript = @"
                INSERT INTO Edges (SourceId, TargetId, Type)
                VALUES
                    (1, 2, 'Friend'),
                    (1, 3, 'Employer');
            ";
            environment.ExecuteScript(insertEdgeDataScript);

            // Verify that data was inserted
            string getNodeDataScript = @"
                SELECT *
                FROM Nodes;
            ";
            IRowset nodeData = environment.ExecuteScript(getNodeDataScript);
            Assert.AreEqual(nodeData.Count(), 3, "Node data was not inserted");

            string getEdgeDataScript = @"
                SELECT *
                FROM Edges;
            ";
            IRowset edgeData = environment.ExecuteScript(getEdgeDataScript);
            Assert.AreEqual(edgeData.Count(), 2, "Edge data was not inserted");
        }
    }
}

[marcialwushu]

Este exemplo define um teste de unidade chamado InsertNodeAndEdgeData. O teste configura um ambiente U-SQL de teste, conecta-se a um banco de dados chamado "MyGraphDB", cria uma tabela de nós chamada "Nodes" e uma tabela de arestas chamada "Edges". O teste insere dados nessas tabelas usando instruções INSERT e verifica se os dados foram inseridos corretamente. O teste usa uma instrução SELECT para recuperar os dados inseridos e verifica se o número de registros é igual ao número de registros que foram inseridos.

[marcialwushu]

-- Create a graph demo database
IF NOT EXISTS (SELECT * FROM sys.databases WHERE NAME = 'graphdemo')
	CREATE DATABASE GraphDemo;
GO

USE GraphDemo;
GO

-- Create NODE tables
CREATE TABLE Person (
  ID INTEGER PRIMARY KEY,
  name VARCHAR(100)
) AS NODE;

CREATE TABLE Restaurant (
  ID INTEGER NOT NULL,
  name VARCHAR(100),
  city VARCHAR(100)
) AS NODE;

CREATE TABLE City (
  ID INTEGER PRIMARY KEY,
  name VARCHAR(100),
  stateName VARCHAR(100)
) AS NODE;

-- Create EDGE tables.
CREATE TABLE likes (rating INTEGER) AS EDGE;
CREATE TABLE friendOf AS EDGE;
CREATE TABLE livesIn AS EDGE;
CREATE TABLE locatedIn AS EDGE;

-- Insert data into node tables. Inserting into a node table is same as inserting into a regular table
INSERT INTO Person (ID, name)
	VALUES (1, 'John')
		 , (2, 'Mary')
		 , (3, 'Alice')
		 , (4, 'Jacob')
		 , (5, 'Julie');

INSERT INTO Restaurant (ID, name, city)
	VALUES (1, 'Taco Dell','Bellevue')
		 , (2, 'Ginger and Spice','Seattle')
		 , (3, 'Noodle Land', 'Redmond');

INSERT INTO City (ID, name, stateName)
	VALUES (1,'Bellevue','WA')
		 , (2,'Seattle','WA')
		 , (3,'Redmond','WA');

-- Insert into edge table. While inserting into an edge table,
-- you need to provide the $node_id from $from_id and $to_id columns.
/* Insert which restaurants each person likes */
INSERT INTO likes
	VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM Restaurant WHERE ID = 1), 9)
		 , ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM Restaurant WHERE ID = 2), 9)
		 , ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
		 , ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9)
		 , ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM Restaurant WHERE ID = 3), 9);

/* Associate in which city live each person*/
INSERT INTO livesIn
	VALUES ((SELECT $node_id FROM Person WHERE ID = 1), (SELECT $node_id FROM City WHERE ID = 1))
		 , ((SELECT $node_id FROM Person WHERE ID = 2), (SELECT $node_id FROM City WHERE ID = 2))
		 , ((SELECT $node_id FROM Person WHERE ID = 3), (SELECT $node_id FROM City WHERE ID = 3))
		 , ((SELECT $node_id FROM Person WHERE ID = 4), (SELECT $node_id FROM City WHERE ID = 3))
		 , ((SELECT $node_id FROM Person WHERE ID = 5), (SELECT $node_id FROM City WHERE ID = 1));

/* Insert data where the restaurants are located */
INSERT INTO locatedIn
	VALUES ((SELECT $node_id FROM Restaurant WHERE ID = 1), (SELECT $node_id FROM City WHERE ID =1))
		 , ((SELECT $node_id FROM Restaurant WHERE ID = 2), (SELECT $node_id FROM City WHERE ID =2))
		 , ((SELECT $node_id FROM Restaurant WHERE ID = 3), (SELECT $node_id FROM City WHERE ID =3));

/* Insert data into the friendOf edge */
INSERT INTO friendOf
	VALUES ((SELECT $NODE_ID FROM Person WHERE ID = 1), (SELECT $NODE_ID FROM Person WHERE ID = 2))
		 , ((SELECT $NODE_ID FROM Person WHERE ID = 2), (SELECT $NODE_ID FROM Person WHERE ID = 3))
		 , ((SELECT $NODE_ID FROM Person WHERE ID = 3), (SELECT $NODE_ID FROM Person WHERE ID = 1))
		 , ((SELECT $NODE_ID FROM Person WHERE ID = 4), (SELECT $NODE_ID FROM Person WHERE ID = 2))
		 , ((SELECT $NODE_ID FROM Person WHERE ID = 5), (SELECT $NODE_ID FROM Person WHERE ID = 4));


-- Find Restaurants that John likes
SELECT Restaurant.name
FROM Person, likes, Restaurant
WHERE MATCH (Person-(likes)->Restaurant)
AND Person.name = 'John';

-- Find Restaurants that John's friends like
SELECT Restaurant.name
FROM Person person1, Person person2, likes, friendOf, Restaurant
WHERE MATCH(person1-(friendOf)->person2-(likes)->Restaurant)
AND person1.name='John';

-- Find people who like a restaurant in the same city they live in
SELECT Person.name
FROM Person, likes, Restaurant, livesIn, City, locatedIn
WHERE MATCH (Person-(likes)->Restaurant-(locatedIn)->City AND Person-(livesIn)->City);

-- Find friends-of-friends-of-friends, excluding those cases where the relationship "loops back".
-- For example, Alice is a friend of John; John is a friend of Mary; and Mary in turn is a friend of Alice.
-- This causes a "loop" back to Alice. In many cases, it is necessary to explicitly check for such loops and exclude the results.
SELECT CONCAT(Person.name, '->', Person2.name, '->', Person3.name, '->', Person4.name)
FROM Person, friendOf, Person as Person2, friendOf as friendOffriend, Person as Person3, friendOf as friendOffriendOfFriend, Person as Person4
WHERE MATCH (Person-(friendOf)->Person2-(friendOffriend)->Person3-(friendOffriendOfFriend)->Person4)
AND Person2.name != Person.name
AND Person3.name != Person2.name
AND Person4.name != Person3.name
AND Person.name != Person4.name;

[marcialwushu]

REFERENCE ASSEMBLY [System.Core];
REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats];

USING System;
USING System.Linq;
USING System.Collections.Generic;
USING Microsoft.Analytics.Samples.Formats;
USING Microsoft.VisualStudio.TestTools.UnitTesting;

namespace GraphDemo.Tests
{
    [TestClass]
    public class GraphDemoTests
    {
        [TestMethod]
        public void TestRestaurantsLikedByJohn()
        {
            string query = @"
                SELECT Restaurant.name
                FROM Person, likes, Restaurant
                WHERE MATCH (Person-(likes)->Restaurant)
                AND Person.name = 'John';
            ";

            List<string> expectedOutput = new List<string>()
            {
                "Taco Dell"
            };

            List<string> actualOutput = new List<string>();
            TestHelpers.ExecuteQuery(query, output => actualOutput.Add(output[0].ToString()));

            CollectionAssert.AreEqual(expectedOutput, actualOutput);
        }

        [TestMethod]
        public void TestRestaurantsLikedByJohnsFriends()
        {
            string query = @"
                SELECT Restaurant.name
                FROM Person person1, Person person2, likes, friendOf, Restaurant
                WHERE MATCH(person1-(friendOf)->person2-(likes)->Restaurant)
                AND person1.name='John';
            ";

            List<string> expectedOutput = new List<string>()
            {
                "Noodle Land"
            };

            List<string> actualOutput = new List<string>();
            TestHelpers.ExecuteQuery(query, output => actualOutput.Add(output[0].ToString()));

            CollectionAssert.AreEqual(expectedOutput, actualOutput);
        }

        [TestMethod]
        public void TestPeopleWhoLikeRestaurantInSameCity()
        {
            string query = @"
                SELECT Person.name
                FROM Person, likes, Restaurant, livesIn, City, locatedIn
                WHERE MATCH (Person-(likes)->Restaurant-(locatedIn)->City AND Person-(livesIn)->City);
            ";

            List<string> expectedOutput = new List<string>()
            {
                "Mary",
                "Alice",
                "Jacob",
                "Julie"
            };

            List<string> actualOutput = new List<string>();
            TestHelpers.ExecuteQuery(query, output => actualOutput.Add(output[0].ToString()));

            CollectionAssert.AreEqual(expectedOutput, actualOutput);
        }

        [TestMethod]
        public void TestFriendsOfFriendsOfFriends()
        {
            string query = @"
                SELECT CONCAT(Person.name, '->', Person2.name, '->', Person3.name, '->', Person4.name)
                FROM Person, friendOf, Person as Person2, friendOf as friendOf2, Person as Person3, friendOf as friendOf3, Person as Person4
                WHERE MATCH(Person-(friendOf)->Person2-(friendOf2)->Person3-(friendOf3)->Person4)
                AND NOT MATCH(Person<-(friendOf)-Person2-(friendOf2)->Person)
                AND NOT MATCH(Person2<-(friendOf2)-Person3-(friendOf3)->Person2)
                AND NOT MATCH(Person3<-(friendOf3)-Person4);
            ";

            List<string> expectedOutput = new List<string>()
            {
                "John->Mary->Alice->Jacob",
                "John->Mary->Alice->Julie"
            };

            List<string> actualOutput = new List<string>();
            TestHelpers.ExecuteQuery(query, output => actualOutput.Add(output[0].ToString()));

            CollectionAssert.AreEqual(expectedOutput, actualOutput);
        }
    }
}

[marcialwushu]

Para adicionar a criação de tabelas no U-SQL Unit Test, você pode seguir os seguintes passos:

1. Crie um arquivo de script U-SQL com a definição das tabelas que deseja criar, no seu caso, o script seria:

CREATE TABLE Restaurant (
  ID INTEGER NOT NULL,
  name VARCHAR(100),
  city VARCHAR(100)
) AS NODE;

CREATE TABLE likes (rating INTEGER) AS EDGE;
CREATE TABLE friendOf AS EDGE;
CREATE TABLE livesIn AS EDGE;
CREATE TABLE locatedIn AS EDGE;

2. Em seguida, crie um novo arquivo de teste U-SQL e adicione o seguinte código no início do arquivo:

REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Utilities];

Este código referencia a biblioteca de utilitários de teste U-SQL necessária para criar a tabela durante a execução do teste.

3. Em seguida, adicione o seguinte código para criar as tabelas dentro do método de teste:

[Test]
public void TestCreateTables()
{
    // define o caminho para o arquivo com o script U-SQL contendo a definição das tabelas
    string scriptFilePath = @"Caminho\Para\O\Arquivo\De\Script.usql";

    // cria o job de teste U-SQL e define o script a ser executado
    TestJob job = new TestJob
    {
        Script = File.ReadAllText(scriptFilePath)
    };

    // executa o job de teste U-SQL
    job.Execute();

    // verifica se as tabelas foram criadas com sucesso
    Assert.IsTrue(job.GetTestResult("Restaurant").Count() == 1);
    Assert.IsTrue(job.GetTestResult("likes").Count() == 1);
    Assert.IsTrue(job.GetTestResult("friendOf").Count() == 1);
    Assert.IsTrue(job.GetTestResult("livesIn").Count() == 1);
    Assert.IsTrue(job.GetTestResult("locatedIn").Count() == 1);
}

Este código carrega o arquivo de script U-SQL com a definição das tabelas, cria um objeto de job de teste U-SQL e define o script a ser executado. Em seguida, executa o job de teste U-SQL e verifica se as tabelas foram criadas com sucesso usando a função GetTestResult e a classe Assert.

Execute o teste U-SQL para criar as tabelas. Se as tabelas forem criadas com sucesso, você verá uma mensagem de sucesso no console do teste. Se houver um erro, você receberá uma mensagem de erro indicando qual teste falhou e qual foi a causa do erro.
Observação: Certifique-se de ter permissão para criar tabelas no banco de dados do Data Lake Analytics antes de executar o teste U-SQL.