Reflection in C#

I was talking with a colleague about reflection, and I started playing with it in LinqPad; for my own reference, here's a small code sample

Getting started with Apache Spark in Python

Apache Spark is one of the promising new technologies for big data processing; it provides distributed in-memory tables and allows you to quickly compute aggregates and even complicated calculations. Spark provides interfaces for Java, Scala and Python.

Although setting a production cluster of Spark can be complicated (especially since you probably want to set up a hadoop cluster too), you can run Spark locally and, in that case, installation is a breeze (download,unpack :).

After you've installed it, running pyspark will get you a shell, and it will create a context (from which you can access most functionality) in a variable called sc.

For this simple tutorial we will load a few files containing sales data, and aggregate in a couple of different ways. You can read any kinds of files, but you need to parse them yourself, so you normally want a simple format; we will read tab-separated files, which contain a fixed number of fields, separated with tabs. Our file will contain the day, the store id, the product id and the amount sold on that day.

To load a file we use the textFile method of the context; you pass it the name of a file (or a regular expression matching more than one file)

For example, to load all of the files that start with sales we would do:
sc.textFile("sales_*.txt")

now, this gives us a collection (spark calls them RDDs) of lines; we normally want to parse each line into its fields; we can call the map method, and give it a function; this will return a collection resulting from applying this method to each of the elements of the previous one (and we can chain this calls). Since we have a tab-delimited file, we just need to call the split method on each line, and that will return an array of strings; so we can do:
sales=sc.textFile("sales_*.txt").map(lambda x:x.split('\t'))

Here we use textFile to read all files that start with sales_ and end in .txt; this would get us a collection of lines; we then apply to each line a function which splits the line by tabs ('\t'), so we now have an array of fields instead of each line; we finally store that in a variable called sales (as an aside, this doesn't actually read the files; instead, spark stores the transformations and won't actually compute it until it's needed; if you want to see the results you can use first to get the first object, or collect to get the full collection).

Once we have the data loaded, we may want to do some calculations; most of the time, we want to calculate values for a group;  something equivalent to a GROUP BY in SQL. We can use the reduceByKey method; this method takes a collection of pairs; will create groups by the first element, and then apply a reducing function to the elements in the group.

Our sales RDD does not contain pairs, so we first need to apply a map, to get pairs; the function we want to apply to the group is addition, so if we want to calculate the sales by day, we can do:
sales_by_day=sales.map(lambda x:(x[0],int(x[3])))       
   .reduceByKey(lambda x,y:x+y)

Remember that the day is the first field (so x[0]) and the amount sold is the fourth element, x[3]. We use reduceByKey to group by the first field (the day) and calculate the sum of the amount sold.

You can look at the elements in sales_by_day with:

sales_by_day.collect()

Which, for our sample data, produces 

[(u'2014-01-02', 212), (u'2014-01-01', 357)]

Many times we want to store the output into another text file; we can use saveAsTextFile; notice that we normally want to apply map again, to transform our output into a more suitable format; for example, to store it again as a text-delimited file we can do:

sales_by_day.map(lambda l: "{0}\t{1}".format(l[0],l[1])) .saveAsTextFile("sales_by_day")

Notice this will actually produce a folder called sales_by_day with a bunch of files, part-0000 etc, and SUCCESS (if it succeeds), just like hadoop would.

Sample data can be obtained from https://github.com/okaram/spark ; I plan to make this into a series; next one will be on joins, another on writing applications, and at least one more on performance.

Using Excel as your UI

At work, part of our job involves getting data from a bunch of different databases and verifying that certain relationships hold among them, or finding other kinds of problems or bugs; we usually end up pasting our results into Excel, and sending the spreadsheets around.

One of our teammates had the idea of writing a script that writes directly to Excel; I'm still amazed it didn't occur to me :) but to compensate, I figured I'd write a program to do the same; since I couldn't find any simple .net libraries to write to excel, I figured I'd try with python; found openpyxl (great simple library, although its docs are outdated), so I wrote a simple script that takes queries in JSON and produces an excel spreadsheet with the data produced by those queries.
The Json file looks like this:
Notice we can specify several sheets, and for every sheet we specify queries; for each query we specify its DBI driver (you need to have those libraries available in your system), connection string and query string.
The python code is amazingly simple:
And you call it by passing it the name of the json file, and the name of the output you want.

NodeJs tips and Tricks

I'm relearning nodejs, for fun; I'm writing here some links I've found useful (more as a reminder to myself)

• Node.js: Using require to load your own files I'm using this technique to hide my passwords :) create a file called mongo-url.js inside node_modules, require it, and put */node_modules/* in my gitignore
• How to read files in node To build my my own 'template' engine :)

Playing with nginx and postgresql

I've always used Apache as my web server (and now MS IIS :), but have wanted to play with nginx; I was looking for something to do with my Raspberry Pi, so I figured setting up nginx's postgresql module would be fun. I followed the instructions for OpenResty (a 'distribution' of nginx with a bunch of modules configured by default; not a lot of documentation, but enough to get started).

I was running raspbian (a debian derivative), so it was as simple as:

apt-get install libreadline-dev libncurses5-dev libpcre3-dev libssl-dev perl make libpq-dev

(notice libpq-dev is not in the docs, but it is needed for the postgresql module, which is not installed by default with openresty)

then download with wget (this is the latest version at the time)

wget http://openresty.org/download/ngx_openresty-1.5.12.1.tar.gz

Uncompress, configure, make and install

tar -xzf ngx_openresty-1.5.12.1.tar.gz
cd openresty-1.5.12.1
./configure --with-luajit --with-http_postgress_module && make && sudo make install

nginx gets installed into /usr/local/openresty/nginx , so we can edit the configuration file with:

sudo vim /usr/local/openresty/nginx/nginx.conf

(I was using vim; use your favorite text editor otherwise :)

And we get to the fun part ; in nginx, we use the server stanza to configure a server; inside the http section, we use upstream to specify how to connect to the database (db is just a name, which we'll use in our other directives)

upstream db {
    postgres_server 127.0.0.1 dbname=test user=test password=abc123;
}

And then inside the server section, we can define specific queries for certain locations; for example:

location /stores {
    rds_json          on;
    postgres_pass   db;
    postgres_query  "SELECT * FROM Stores";
}

Specifies that when the user visits /stores, we should return (as json) the result of executing SELECT * from Stores.

Getting running queries in SQL Server

Today I needed to get running queries in SQL server (you can kill them with the command KILL followed by the session id). After 'binging' it, got to the following query :). Need to study more about the metaschema in sql server.

SELECT session_id,
       request_id,
       Db_name(database_id),
       start_time,
       status,
       command,
       Substring(txt.TEXT, ( statement_start_offset / 2 ) + 1,
       ( ( CASE statement_end_offset
       WHEN -1 THEN Datalength(txt.TEXT)
       ELSE statement_end_offset
                                                                   END
           - statement_start_offset ) / 2 ) + 1) AS statement_text,
       wait_type,
       wait_time,
       blocking_session_id,
       percent_complete,
       cpu_time,
       reads,
       writes,
       logical_reads,
       row_count
FROM   sys.dm_exec_requests
       CROSS APPLY sys.Dm_exec_sql_text([sql_handle]) AS txt
WHERE  session_id <> @@SPID
       AND session_id > 50

Automatas (finite state machines): A programmer's perspective

Finite state machines are simple and nifty; when I was teaching theory, I saw students, even good programmers, had trouble with them when presented as math, so I figure showing them as programs could make it click for some people.

From http://cg.scs.carleton.ca/~michiel/TheoryOfComputation/ (a free ToC book)

Definition 2.2.1 A finite automaton is a 5-tuple M = (Q, Σ, δ, q, F), where
1. Q is a finite set, whose elements are called states,
2. Σ is a finite set, called the alphabet; the elements of Σ are called symbols,
3. δ : Q × Σ → Q is a function, called the transition function,
4. q is an element of Q; it is called the start state,
5. F is a subset of Q; the elements of F are called accept states.

Doesn't that sound fancy ? It means we have an object with 5 fields (a 5-tuple), Q, a set of things called states, Σ, a set of symbols called the alphabet (we'll use characters as our symbols), δ, a function that takes a state and a symbol and returns a state (conceptually we are 'moving' from one state to another when we see a symbol), q, an element of Q , called the start state and F a subset of Q, the set of accepting states.

In Scala, we can represent it as:


We can then define the extended transition function, which says which state we end up at by following a string, recursively as

This means if the string is empty, we stay at the state we're at; if not, we follow the first character (by using delta), and then follow the rest of the string from there; this recursive definition maps nicely to a List in scala (or any other language with cons-style lists :), so we end up with the following code:


And the automata accepts a string if we would go from the initial state to the final state by following that string; in Scala
To use it we would first define a delta function, say:
And then we can use like this:
The full code is at https://github.com/okaram/scala/tree/master/automata, including non-deterministic automata (which I will probably get around to blog about some other time :)

Accessing Databases with R (RStudio)

R has libraries for accessing several databases, but if you're on Windows, you'd probably prefer the RODBC package; with this, you can access almost any db through ODBC. You can create a DSN and use odbcConnect, or use odbcDriverconnect ; the problem is figuring out the connection string, especially since you need to specify the driver. For SQL Server, this works (assuming db is in localhost):

library(RODBC)

c=odbcDriverConnect('Driver=SQL Server;Server=localhost;Database=......;Trusted_Connection=True')

And then you can do odbcquery like:

res=sqlQuery(c,"SELECT * FROM ....")

And you get a data frame !

http://www.connectionstrings.com/ has odbc connection strings for many dbs

Super quick Linq to XML

I was trying to figure out how to get started with Linq to XML, and found this article. I figured I'd simplify it even more for myself :)
Imagine we have the following file, saved in c:\contacts.xml

Then we can access it as follows
Notice:

• on an XDocument, we can call descendants and pass it an element name, to get all the elements with that name (we can also call the no-args version, and get all descendants)
• What we're getting is an XElement, on which we can call methods like Element or Attribute
• We can directly cast an element into a string, and it gets us all the text inside that element
• We can cast an attribute as a string, or an int (or even a float, double or many other types)

C# is pretty expresive too

I just saw this post , about how to add methods to types in F# (and referencing another in rust), so you can make words like Months and Ago have meaning and allow you to say things like '3 years ago'. Since C# now has extension methods, I figured I'd try to see how it could be done in C#


So, what do you think ?

BTW, I tested this in LinqPad, may need something extra to become a full program

Personal mongodb Cheatsheet

I'm trying to learn more mongodb (taking one of their free courses), so I figured I'll keep here the things that I use often (and maybe it will be useful to others).

Printing

You can use the print method to print in the shell print('hello')

Iterating over a cursor

The value returned by find and findOne is not a collection but a 'cursor', so you can't just do 

for(var obj in db.xyz.find())

you need to do

var cursor=db.xyz.find()

while(cursor.hasNext()) {

  obj=cursor.next();

  ...

}

or define a function and use forEach()

Azure for hosting HTML files

I'll be teaching a friend some HTML, and was looking for an easy, free host for playing sites; Azure has free websites, but I was afraid I'd need to teach them webmatrix of VS express (nothing against them, but it is one more thing to learn). I just discovered Azure can deploy sites from a git repo !! yipee !

XML queries in MS SQL Server

Recently I needed to extract some data that was stored as XML in a SQL Server 2012 db; I found the documentation lacking, mainly because you need to know both MS proprietary SQL extensions and XPath, to query the xml. Here's what I figured; it may not be optimal, but it works :) (BTW, I think Server 2008 has the same XML methods, but I only tested with 2012).

The xml type

SQL server supports an XML datatype; we use singlle-quotes to denote its values, but it is not quite a varchar type. Below we define a table Person, with id, name and an XML field called SkillsXML; we insert two rows. The xml looks like <skills>  <skill level="High"> SQL</skill> ... </skills>.

Simple queries

With this, we can use the query method of the xml datatype to select only certain nodes; for example, the following query would select only the first skill for each person. Notice we're using an XPath expression for selecting here; simple XPath expressions look like folders; here we're selecting from the root of the document, the skills node, and from there the first skill node (notice XPath starts counting at 1, not 0). Notice the value returned is of type xml, not a varchar, which is fine to just look at it, but you may want to cast it as a varchar to use it in other places.

Getting the value of an attribute

We can also use the value method, which allows us to get the value of an XML attribute; this method takes 2 arguments, an XPath expression and a string with a SQL datatype; the value of the XPath expression will be cast (or converted) to that type. The following query gets the value of the level attribute of the first skill.

Nodes queries

The most interesting queries use the nodes xml operator; with this, and cross apply (or outer apply) we can get one row per xml element; for example, the following query gets us each person with their skills; since each of our person rows has 2 skills, we will get 4 rows total. We can make this much nicer by using a WITH expression, selecting both the skill name (which is text inside the element and its level attribute, and casting each of them as a varchar; the following example selects only those skills with Medium level.

To Do

I will post examples using XML namespaces, and will add links to other references soon.

Web api and Dapper for super-simple REST APIs

Recently c# got the dynamic keyword, which allows for truly dynamic typing (like in Ruby, Python or Javascript), and allow you to define objects to which you can add fields at runtime. In fact, we now have ExpandoObject in the standard library.

An obvious place to take advantage of that is with communicating to databases; Most times, we're still using a relational database, and use SQL to communicate with it. SQL is dynamically typed (at least in the sense that your types are coming from a database, independently of your program), and translating it to our objects usually involves lots of gruntwork; it would be nice if the language helps with that gruntwork; with dynamic and ExpandoObject being available, there are now a few micro-frameworks for DB access; one of the simplest is Dapper ; it just adds a few extension methods to the SqlConnection class, and can either automatically put data into our classes, or into dynamic objects (which is what we'll do).

The new-ish web api in ASP.net automatically generates REST APIs with very little code; with that, and Dapper, we can give access to our database in very little time; for example, if we have a database with one table, called People, with two fields, id, and name; we can generate a controller with code as simple as:


Of course, eventually you may want to use a more complete ORM, like EntityFramework, but for quick prototyping, it doesn't get any simpler than this.

Vlookup - doing joins in excel

I've been doing some data analysis in excel (and watching somebody who really knows excel :) one of the useful functions I've learned is vlookup, which basically lets you do a join (like in SQL) ; you pass it the value to find, a range of cells over which to look, an index indicating which column to return and a boolean indicating whether to do exact or approximate matching (usually you pass false, to ask for exact matching, a behavior like SQL)

invoke-webrequest : wget for Windows

Many times you need to do a quick test that a web site or program works, or you want to download a web page within a script; in unix/linux, you'd probably use curl or wget ; Powershell (as of 3.0) has invoke-webrequest , which does a similar function.

Although it has many other options, the most basic invocation just takes a URL and it returns the 'output' of that request, as an object containing several fields; the Content field contains the actual information returned (the web page), so you can use something like this:

(Invoke-WebRequest http://okaram.com).Content.length

to find out its length, and could use String.Contains and such to verify that it returns the info you expected (so you could script a simpletest for your web app).

Other useful parameters include -OutFile to save the page to a file, and -UseDefaultCredentials to have the request use your windows credentials, in an intranet.

Powershell: Array2Object function

I've been using Powershell a lot recently (I'll be blogging a lot more about it soon); sometimes, you get an array and it is convenient to transform it into an object (so we can apply select and similar operators to it); objects in powershell can be created with the New-Object cmdlet, and we pass it the kind of object; the basic powershell object is of type psobject, and we can use Add-Member to add a field to it (powershell objects, like in most scripting languages, can have fields added dynamically to them)

The following function will create an object, given an array ; the object have fields f1,f2 ... corresponding to the array elements 0,1,...

function array2Object($arr)
{
    $obj=New-Object psobject
    $fn=0;
    foreach($field in $arr) {
        ++$fn;
        Add-Member -InputObject $obj -MemberType NoteProperty -Name "f$fn" -value $field
    }
    return $obj
}

Categorizing your unit tests in C# (Visual Studio)

C# comes with a unit testing framework (at least in Visual Studio :). You write a test case like this:

[TestMethod]
public void TestMethod1() ...

When you have more than a few test cases, it is useful to break them into groups or categories; you can use:

• Owner("name")
• TestCategory("category")
• TestProperty("name","value")

For example:

[TestMethod, Owner("Orlando"),TestCategory("stuff"),TestProperty("group","1")]

public void TestMethod1() ...

On the test explorer window, VS will show you your tests organized based on those values, and you can then select and run only those tests you want.

Improving your command line in windows

I've just read Making a better, somewhat prettier, but definitely more functional Windows Command Line, and tried a couple of his suggestions, since I'll be using Windows a lot more.

I already knew (and had installed) Git-For-Windows (I called it git-bash :), which is incredibly cool, since it installs bash and a bunch of Unix commands (including ssh, and which :) and have been using MobaXTerm (which also includes an XWindows server, and so it's great for communicating with Linux servers  (and I know I will be learning powershell :)

But I got to try Clink which is simple but amazingly useful; it embeds Gnu's readline into cmd.exe, so your command terminal uses tabs for autocompletion (and shows the choices, like bash does), and uses 'normal' (well the ones my fingers know :) keybindings, like Ctrl-A to go to the beginning, Ctrl-E to go to the end, and Ctrl-R to search your history.

I also installed Gow, which installs a bunch of Gnu utilities; I didn't find it that useful, since most of them come with git-for-windows, but includes wget (which I always use :) and adds a menu to the file manager that allows you to open a command prompt in a given folder, which is useful.

Retrying things in ruby

Working with remote systems that are not always under your control often involves failures; I'm working on a system that does an API call on a remote system, and the remote system needs to call other systems to finish the call (it is calling a licensing server, from another company). The call already has timeouts and delays, but it crashes often enough, which requires me to restart the job (the job is inside a resque queue).

Now, if we try to do something, and it fails, it will usually raise an exception; Ruby has exception handling, including the rescue keyword, which will restart the block that started the exception; Ruby also has blocks, procs and lambdas, which allow us to pass a piece of code (OK, a closure :) to another function or method, so we can define a function as follows:

And then we could use it like:
Voila ! we now can easily retry our operation several times; if we wanted to be fancy, we could add a time to sleep between tries, and even define a small DSL for this, but for my needs this was enough.