/// <summary>Verifies the results are correct</summary>
/// <exception cref="Java.Sql.SQLException"/>
private bool Verify()
{
//check total num pageview
string countAccessQuery = "SELECT COUNT(*) FROM Access";
string sumPageviewQuery = "SELECT SUM(pageview) FROM Pageview";
Statement st = null;
ResultSet rs = null;
try
{
st = connection.CreateStatement();
rs = st.ExecuteQuery(countAccessQuery);
rs.Next();
long totalPageview = rs.GetLong(1);
rs = st.ExecuteQuery(sumPageviewQuery);
rs.Next();
long sumPageview = rs.GetLong(1);
Log.Info("totalPageview=" + totalPageview);
Log.Info("sumPageview=" + sumPageview);
return(totalPageview == sumPageview && totalPageview != 0);
}
finally
{
if (st != null)
{
st.Close();
}
if (rs != null)
{
rs.Close();
}
}
}
public void QueryFilter()
{
string nameFilter = "Bill";
string passFilter = "hknfpkj";
Statement stmt = new Statement();
stmt.SetNamespace(args.ns);
stmt.SetSetName(args.set);
stmt.SetFilters(Filter.Equal(binName, nameFilter));
stmt.SetAggregateFunction(Assembly.GetExecutingAssembly(), "Aerospike.Test.Resources.filter_example.lua", "filter_example", "profile_filter", Value.Get(passFilter));
// passFilter will be applied in filter_example.lua.
ResultSet rs = client.QueryAggregate(null, stmt);
try
{
int count = 0;
while (rs.Next())
{
IDictionary map = (IDictionary)rs.Object;
Assert.AreEqual(nameFilter, map["name"]);
Assert.AreEqual(passFilter, map["password"]);
count++;
}
Assert.AreNotEqual(0, count);
}
finally
{
rs.Close();
}
}
private void RunQuery(AerospikeClient client, Arguments args, string indexName, string binName, string packageContents)
{
int begin = 4;
int end = 7;
console.Info("Query for:ns={0} set={1} index={2} bin={3} >= {4} <= {5}",
args.ns, args.set, indexName, binName, begin, end);
Statement stmt = new Statement();
stmt.SetNamespace(args.ns);
stmt.SetSetName(args.set);
stmt.SetBinNames(binName);
stmt.SetFilter(Filter.Range(binName, begin, end));
stmt.SetAggregateFunction("sum_example", packageContents, "sum_single_bin", Value.Get(binName));
ResultSet rs = client.QueryAggregate(null, stmt);
try
{
int expected = 22; // 4 + 5 + 6 + 7
int count = 0;
while (rs.Next())
{
object obj = rs.Object;
if (obj is long)
{
long sum = (long)rs.Object;
if (expected == (int)sum)
{
console.Info("Sum matched: value=" + expected);
}
else
{
console.Error("Sum mismatch: Expected {0}. Received {1}.", expected, sum);
}
}
else
{
console.Error("Unexpected return value: " + obj);
continue;
}
count++;
}
if (count == 0)
{
console.Error("Query failed. No records returned.");
}
}
finally
{
rs.Close();
}
}
} //updatePasswordUsingUDF
public void aggregateUsersByTweetCountByRegion()
{
ResultSet rs = null;
try
{
int min;
int max;
Console.WriteLine("\nEnter Min Tweet Count:");
min = int.Parse(Console.ReadLine());
Console.WriteLine("Enter Max Tweet Count:");
max = int.Parse(Console.ReadLine());
// NOTE: UDF registration has been included here for convenience and to demonstrate the syntax.
// NOTE: The recommended way of registering UDFs in production env is via AQL
string luaDirectory = @"..\..\udf";
LuaConfig.PackagePath = luaDirectory + @"\?.lua";
string filename = "aggregationByRegion.lua";
string path = Path.Combine(luaDirectory, filename);
RegisterTask rt = client.Register(null, path, filename, Language.LUA);
rt.Wait();
string[] bins = { "tweetcount", "region" };
Statement stmt = new Statement();
stmt.SetNamespace("test");
stmt.SetSetName("users");
stmt.SetIndexName("tweetcount_index");
stmt.SetBinNames(bins);
stmt.SetFilters(Filter.Range("tweetcount", min, max));
Console.WriteLine("\nAggregating users with " + min + "-" + max + " tweets by region. Hang on...\n");
rs = client.QueryAggregate(null, stmt, "aggregationByRegion", "sum");
if (rs.Next())
{
Dictionary <object, object> result = (Dictionary <object, object>)rs.Object;
Console.WriteLine("Total Users in North: " + result["n"]);
Console.WriteLine("Total Users in South: " + result["s"]);
Console.WriteLine("Total Users in East: " + result["e"]);
Console.WriteLine("Total Users in West: " + result["w"]);
}
}
finally
{
if (rs != null)
{
// Close record set
rs.Close();
}
}
} //aggregateUsersByTweetCountByRegion
private void TryAndCloseConnection(Connection connection)
{
Statement stmt = connection.CreateStatement();
stmt.Execute("select 1 from dual");
ResultSet result = stmt.ResultSet;
result.Next();
Assert.AreEqual(1, result.GetInt(1));
result.Close();
stmt.Close();
connection.Close();
}
private void RunQuery(AerospikeClient client, Arguments args, string indexName, string binName)
{
console.Info("Query for:ns={0} set={1} index={2} bin={3}",
args.ns, args.set, indexName, binName);
Statement stmt = new Statement();
stmt.SetNamespace(args.ns);
stmt.SetSetName(args.set);
stmt.SetFilter(Filter.Equal(binName, 1));
ResultSet rs = client.QueryAggregate(null, stmt, "average_example", "average");
try
{
if (rs.Next())
{
object obj = rs.Object;
if (obj is Dictionary <object, object> )
{
Dictionary <object, object> map = (Dictionary <object, object>)obj;
object objsum = map["sum"];
object objcount = map["count"];
double sum = (double)(long)objsum;
double count = (double)(long)objcount;
double avg = sum / count;
console.Info("Sum=" + sum + " Count=" + count + " Average=" + avg);
double expected = 5.5;
if (avg != expected)
{
console.Error("Data mismatch: Expected {0}. Received {1}.", expected, avg);
}
}
else
{
console.Error("Unexpected object returned: " + obj);
}
}
else
{
console.Error("Query failed. No records returned.");
}
}
finally
{
rs.Close();
}
}
private void RunQuery(AerospikeClient client, Arguments args, string indexName, string binName1, string binName2)
{
StringBuilder rgnsb = new StringBuilder();
rgnsb.Append("{ ");
rgnsb.Append(" \"type\": \"Polygon\", ");
rgnsb.Append(" \"coordinates\": [ ");
rgnsb.Append(" [[-122.500000, 37.000000],[-121.000000, 37.000000], ");
rgnsb.Append(" [-121.000000, 38.080000],[-122.500000, 38.080000], ");
rgnsb.Append(" [-122.500000, 37.000000]] ");
rgnsb.Append(" ] ");
rgnsb.Append(" } ");
console.Info("QueryRegion: " + rgnsb);
string amenStr = "school";
Statement stmt = new Statement();
stmt.SetNamespace(args.ns);
stmt.SetSetName(args.set);
stmt.SetFilter(Filter.GeoWithinRegion(binName1, rgnsb.ToString()));
stmt.SetAggregateFunction("geo_filter_example", "match_amenity", Value.Get(amenStr));
ResultSet rs = client.QueryAggregate(null, stmt);
try
{
int count = 0;
while (rs.Next())
{
object result = rs.Object;
console.Info("Record found: " + result);
count++;
}
if (count != 2)
{
console.Error("Wrong number of schools found. %d != 2", count);
}
}
finally
{
rs.Close();
}
}
public void QuerySum()
{
int begin = 4;
int end = 7;
Statement stmt = new Statement();
stmt.SetNamespace(args.ns);
stmt.SetSetName(args.set);
stmt.SetBinNames(binName);
stmt.SetFilters(Filter.Range(binName, begin, end));
stmt.SetAggregateFunction(Assembly.GetExecutingAssembly(), "Aerospike.Test.Resources.sum_example.lua", "sum_example", "sum_single_bin", Value.Get(binName));
ResultSet rs = client.QueryAggregate(null, stmt);
try
{
int expected = 22; // 4 + 5 + 6 + 7
int count = 0;
while (rs.Next())
{
object obj = rs.Object;
long sum = 0;
if (obj is long)
{
sum = (long)rs.Object;
}
else
{
Assert.Fail("Return value not a long: " + obj);
}
Assert.AreEqual(expected, (int)sum);
count++;
}
Assert.AreNotEqual(0, count);
}
finally
{
rs.Close();
}
}
public void QueryAverage()
{
Statement stmt = new Statement();
stmt.SetNamespace(args.ns);
stmt.SetSetName(args.set);
stmt.SetFilters(Filter.Range(binName, 0, 1000));
stmt.SetAggregateFunction(Assembly.GetExecutingAssembly(), "Aerospike.Test.Resources.average_example.lua", "average_example", "average");
ResultSet rs = client.QueryAggregate(null, stmt);
try
{
if (rs.Next())
{
object obj = rs.Object;
if (obj is IDictionary)
{
IDictionary map = (IDictionary)obj;
long sum = (long)map["sum"];
long count = (long)map["count"];
double avg = (double)sum / count;
Assert.AreEqual(5.5, avg, 0.00000001);
}
else
{
Assert.Fail("Unexpected object returned: " + obj);
}
}
else
{
Assert.Fail("Query Assert.Failed. No records returned.");
}
}
finally
{
rs.Close();
}
}
private void RunQuery(AerospikeClient client, Arguments args, string indexName, string binName)
{
string nameFilter = "Bill";
string passFilter = "hknfpkj";
console.Info("Query for: ns=%s set=%s index=%s name=%s pass=%s", args.ns, args.set, indexName, nameFilter, passFilter);
Statement stmt = new Statement();
stmt.SetNamespace(args.ns);
stmt.SetSetName(args.set);
stmt.SetFilters(Filter.Equal(binName, nameFilter));
stmt.SetAggregateFunction("filter_example", "profile_filter", Value.Get(passFilter));
// passFilter will be applied in filter_example.lua.
ResultSet rs = client.QueryAggregate(null, stmt);
try
{
int count = 0;
while (rs.Next())
{
Dictionary <object, object> map = (Dictionary <object, object>)rs.Object;
Validate(map, "name", nameFilter);
Validate(map, "password", passFilter);
count++;
}
if (count == 0)
{
console.Error("Query failed. No records returned.");
}
}
finally
{
rs.Close();
}
}
/// <summary>
/// <inheritDoc/>
///
/// </summary>
/// <exception cref="System.IO.IOException"/>
public override void Close()
{
try
{
if (null != results)
{
results.Close();
}
if (null != statement)
{
statement.Close();
}
if (null != connection)
{
connection.Commit();
connection.Close();
}
}
catch (SQLException e)
{
throw new IOException(e.Message);
}
}
} //updatePasswordUsingCAS
public void aggregateUsersByTweetCountByRegion()
{
// TODO: Create NUMERIC index on tweetcount in users set (Same as Exercise Q4)
// Exercise A2
// NOTE: Index creation has been included in here for convenience and to demonstrate the syntax
// The recommended way of creating indexes in production env is via AQL
// or create once using a standalone application.
//IndexTask task = client.CreateIndex(null, "test", "users", "tweetcount_index", "tweetcount", IndexType.NUMERIC);
//task.Wait();
ResultSet rs = null;
try
{
int min;
int max;
Console.WriteLine("\nEnter Min Tweet Count:");
min = int.Parse(Console.ReadLine());
Console.WriteLine("Enter Max Tweet Count:");
max = int.Parse(Console.ReadLine());
// TODO: Register UDF
// Exercise A2
// NOTE: UDF registration has been included here for convenience and to demonstrate the syntax.
// The recommended way of registering UDFs in production env is via AQL
// or standalone application using code similar to below.
string luaDirectory = @"..\..\udf";
LuaConfig.PackagePath = luaDirectory + @"\?.lua";
string filename = "aggregationByRegion.lua";
string path = Path.Combine(luaDirectory, filename);
RegisterTask rt = client.Register(null, path, filename, Language.LUA);
rt.Wait();
// TODO: Create string array of bins that you would like to retrieve
// In this example, we want to display which region has how many tweets.
// Exercise A2
string[] bins = { "tweetcount", "region" };
// TODO: Create Statement instance
// Exercise A2
Statement stmt = new Statement();
// TODO: Set namespace on the instance of the Statement
// Exercise A2
stmt.SetNamespace("test");
// TODO: Set the name of the set on the instance of the Statement
// Exercise A2
stmt.SetSetName("users");
// TODO: Set the name of index on the instance of the Statement
// Exercise A2
stmt.SetIndexName("tweetcount_index");
// TODO: Set the list of bins to retrieve on the instance of the Statement
// Exercise A2
stmt.SetBinNames(bins);
// TODO: Set the range Filter on tweetcount on the instance of the Statement
// Exercise A2
stmt.SetFilters(Filter.Range("tweetcount", min, max));
Console.WriteLine("\nAggregating users with " + min + "-" + max + " tweets by region. Hang on...\n");
// TODO: Execute the Aggregation Query passing null policy and Statement instance,
// Lua Module and module function to call.
// Exercise A2
rs = client.QueryAggregate(null, stmt, "aggregationByRegion", "sum");
if (rs.Next())
{
// TODO: Iterate through returned RecordSet and output text in format "Total Users in <region>: <#>"
// Exercise A2
Dictionary <object, object> result = (Dictionary <object, object>)rs.Object;
Console.WriteLine("Total Users in North: " + result["n"]);
Console.WriteLine("Total Users in South: " + result["s"]);
Console.WriteLine("Total Users in East: " + result["e"]);
Console.WriteLine("Total Users in West: " + result["w"]);
}
}
finally
{
// TODO: Close the RecordSet
// Exercise A2
if (rs != null)
{
// Close record set
rs.Close();
}
}
} //aggregateUsersByTweetCountByRegion
/// <summary>
/// <inheritDoc/>
///
/// </summary>
/// <exception cref="System.IO.IOException"/>
public override IList <InputSplit> GetSplits(JobContext job)
{
ResultSet results = null;
Statement statement = null;
try
{
statement = connection.CreateStatement();
results = statement.ExecuteQuery(GetCountQuery());
results.Next();
long count = results.GetLong(1);
int chunks = job.GetConfiguration().GetInt(MRJobConfig.NumMaps, 1);
long chunkSize = (count / chunks);
results.Close();
statement.Close();
IList <InputSplit> splits = new AList <InputSplit>();
// Split the rows into n-number of chunks and adjust the last chunk
// accordingly
for (int i = 0; i < chunks; i++)
{
DBInputFormat.DBInputSplit split;
if ((i + 1) == chunks)
{
split = new DBInputFormat.DBInputSplit(i * chunkSize, count);
}
else
{
split = new DBInputFormat.DBInputSplit(i * chunkSize, (i * chunkSize) + chunkSize
);
}
splits.AddItem(split);
}
connection.Commit();
return(splits);
}
catch (SQLException e)
{
throw new IOException("Got SQLException", e);
}
finally
{
try
{
if (results != null)
{
results.Close();
}
}
catch (SQLException)
{
}
try
{
if (statement != null)
{
statement.Close();
}
}
catch (SQLException)
{
}
CloseConnection();
}
}
/// <summary>
/// <inheritDoc/>
///
/// </summary>
/// <exception cref="System.IO.IOException"/>
public override IList <InputSplit> GetSplits(JobContext job)
{
int targetNumTasks = job.GetConfiguration().GetInt(MRJobConfig.NumMaps, 1);
if (1 == targetNumTasks)
{
// There's no need to run a bounding vals query; just return a split
// that separates nothing. This can be considerably more optimal for a
// large table with no index.
IList <InputSplit> singletonSplit = new AList <InputSplit>();
singletonSplit.AddItem(new DataDrivenDBInputFormat.DataDrivenDBInputSplit("1=1",
"1=1"));
return(singletonSplit);
}
ResultSet results = null;
Statement statement = null;
try
{
statement = connection.CreateStatement();
results = statement.ExecuteQuery(GetBoundingValsQuery());
results.Next();
// Based on the type of the results, use a different mechanism
// for interpolating split points (i.e., numeric splits, text splits,
// dates, etc.)
int sqlDataType = results.GetMetaData().GetColumnType(1);
DBSplitter splitter = GetSplitter(sqlDataType);
if (null == splitter)
{
throw new IOException("Unknown SQL data type: " + sqlDataType);
}
return(splitter.Split(job.GetConfiguration(), results, GetDBConf().GetInputOrderBy
()));
}
catch (SQLException e)
{
throw new IOException(e.Message);
}
finally
{
// More-or-less ignore SQL exceptions here, but log in case we need it.
try
{
if (null != results)
{
results.Close();
}
}
catch (SQLException se)
{
Log.Debug("SQLException closing resultset: " + se.ToString());
}
try
{
if (null != statement)
{
statement.Close();
}
}
catch (SQLException se)
{
Log.Debug("SQLException closing statement: " + se.ToString());
}
try
{
connection.Commit();
CloseConnection();
}
catch (SQLException se)
{
Log.Debug("SQLException committing split transaction: " + se.ToString());
}
}
}
} //main
public void aggregateUsersByTweetCountByRegion(AerospikeClient client)
{
ResultSet rs = null;
try
{
int min;
int max;
Console.WriteLine("\nEnter Min Tweet Count:");
min = int.Parse(Console.ReadLine());
Console.WriteLine("Enter Max Tweet Count:");
max = int.Parse(Console.ReadLine());
Console.WriteLine("\nAggregating users with " + min + "-" + max + " tweets by region. Hang on...\n");
// NOTE: Index creation has been included in here for convenience and to demonstrate the syntax.
// NOTE: The recommended way of creating indexes in production env is via AQL.
IndexTask task = client.CreateIndex(null, "test", "testusers", "tweetcountindex", "tweetcount", IndexType.NUMERIC);
task.Wait();
// NOTE: UDF registration has been included here for convenience and to demonstrate the syntax.
// NOTE: The recommended way of registering UDFs in production env is via AQL
string luaDirectory = @"..\..\udf";
LuaConfig.PackagePath = luaDirectory + @"\?.lua";
string filename = "aggregationByRegion.lua";
string path = Path.Combine(luaDirectory, filename);
RegisterTask rt = client.Register(null, path, filename, Language.LUA);
rt.Wait();
Statement stmt = new Statement();
stmt.SetNamespace("test");
stmt.SetSetName("testusers");
stmt.SetIndexName("tweetcountindex");
stmt.SetFilters(Filter.Range("tweetcount", min, max));
rs = client.QueryAggregate(null, stmt, "aggregationByRegion", "sum");
if (rs.Next())
{
Dictionary <object, object> result = (Dictionary <object, object>)rs.Object;
Console.WriteLine("Here's the breakdown...\n");
Console.WriteLine("Total Users in North: " + result["n"]);
Console.WriteLine("Total Users in South: " + result["s"]);
Console.WriteLine("Total Users in East: " + result["e"]);
Console.WriteLine("Total Users in West: " + result["w"]);
}
}
catch (AerospikeException e)
{
Console.WriteLine("AerospikeException - Message: " + e.Message);
Console.WriteLine("AerospikeException - StackTrace: " + e.StackTrace);
}
finally
{
if (rs != null)
{
// Close record set
rs.Close();
}
}
} //aggregateUsersByTweetCountByRegion
