neuecc / linq-to-bigquery Goto Github PK

I am trying to use this in LinqPad.

What should go in the Json Authentication Key? Is it the file?

What should go in the User textbox?

It would help if there is some writeup on this.

Thank you!

The syntax order is

From.Join.Select.[Where/GroupBy.Having].OrderBy.Limit

not select is last.

select repository_language as lang, count(*) as langCount
from [publicdata:samples.github_timeline]
group by lang
having lang is not null
order by langCount desc

difference with IFromBigQueryable

currently we needs .Value when nullable.

for example : http://googlecloudplatform.blogspot.jp/2014/08/correlating-patterns-of-world-history-with-bigquery.html

    var context = Query.GetContext();
    var baseQuery = context
    .From<country_date_matconf_numarts>()
    .JoinCross(context.From<numbers_255>().Where(x => x.i < 60).Select(x => new { x.i }), (a,b) => new { a,b})
    .Select(x => new { x.a.country, ending_at = x.a.date + x.b.i * 86400000000, x.a.c, x.b.i })
    .Into();

    baseQuery 
    .Join(baseQuery.Where(x => x.country == "Egypt" && x.ending_at == BqFunc.ParseUtcUsec("2011-01-27")).Select(), (b,a)=>new{b,a}, x => x.a.i == x.b.i)
    .Where(x => x.a.ending_at != x.b.ending_at)
    .Select(x => new
    {
        ending_at1 = BqFunc.StrftimeUtcUsec(x.a.ending_at.Value, "%Y-%m-%d"),
        starting_at2 = BqFunc.StrftimeUtcUsec(x.b.ending_at.Value - 60*86400000000, "%Y-%m-%d"),
        ending_at2 = BqFunc.StrftimeUtcUsec(x.b.ending_at.Value, "%Y-%m-%d"),
        a_country = x.a.country,
        b_country = x.b.country,
        corr = BqFunc.Correlation(x.a.c.Value, x.b.c.Value),
        c = BqFunc.Count()
    })
    .GroupBy(x => new { x.ending_at1, x.ending_at2, x.starting_at2, x.a_country, x.b_country}, each:true)
    .Having(x => x.c == 60 && BqFunc.Abs(x.corr) > 0.254)
    .OrderByDescending(x => x.corr)
    .Run()
    .Dump();

[TableName("[fh-bigquery:public_dump.numbers_255]")]
public class numbers_255
{
    public long? i { get; set; }
}

[TableName("[gdelt-bq:sample_views.country_date_matconf_numarts]")]
public class country_date_matconf_numarts
{
    public string country { get; set; }
    public long? date { get; set; }
    public long? c { get; set; }
}

• Aggregate functions
• Arithmetic operators
• Bitwise functions
• Casting functions
• Comparison functions
• Date and time functions
• IP functions
• JSON functions
• Logical operators
• Mathematical functions
• Regular expression functions
• String functions
• Table wildcard functions
• URL functions
• Window functions
• Other functions

and return ExpandoObject overload

DevDeploy.bat should check if LINQPad is installed.

when I tried to put any where condition to my query an exception is thrown with The type initializer for 'BigQuery.Linq.BigQueryTranslateVisitor' threw an exception.'

while if I didn't add any condition the query run successfully.

My codes is :

var result = context.From<Data>()
               .Where(x => x.First_Name == "Mahmmod")
               .Select()
               .Limit(1000)
               .Run();

Note I am using ".Net 5"

decimal DataType is being converted to INTEGER instead of FLOAT

Hi,

When the Linqpad's driver is fetching the schema I get the following error:
Error: Exception - Cannot compile typed context: 'location': member names cannot be the same as their enclosing type (line 221)

I don't have a any location column in my schema so I'm not sure where this comes from.
It used to work well (3 months ago).

Any idea?
Thanks in advance

Typo:)

http://www.linqpad.net/extensibility.aspx

$0-of-3
$[index]-of-[count]

Looks like this doesn't support newer versions of exporting the Json auth from Google Cloud? I was getting parser errors while trying to auth through the dialog until structuring my private_key as follows:

{"private_key": "-----BEGIN PRIVATE KEY-----myprivatekeyasdaisdjaojdsiajdois\n-----END PRIVATE KEY-----\n"}

Following that the following error is returned: 'At least one client secrets should be set'

I'll check it.
https://twitter.com/kwilson81/status/697765073171906562

http://stackoverflow.com/questions/22734777/how-do-i-use-the-table-query-function-in-bigquery

• table_id: name of the table.
• creation_time: time, in milliseconds since 1/1/1970 UTC, that the table was created. This is the same as the creation_time field on the table.
• last_modified_time: time, in milliseconds since 1/1/1970 UTC, that the table was updated (either metadata or table contents). Note that if you use the tabledata.insertAll() to stream records to your table, this might be a few minutes out of date.
• type: whether it is a view (2) or regular table (1).
• row_count: number of rows in the table.
• size_bytes: total size in bytes of the table.

SELECT * FROM publicdata:samples.__TABLES__

AppVeyor offers a free CI service for open-source .NET projects. This should be set up for this project to check that all tests pass for a PR before merging. This can only be set up by the owner of the project or a collaborator. See:
http://www.appveyor.com/

I encountered an issue with FormatExceptions appearing when running on a server with the system region format set to Dutch (Netherlands). This is because they use a comma for instead of a decimal point in their numbers. This also affects dates because of the way they converted to unix style longs.

Some of the unit tests break as a result. To recreate, switch your system "region and language" formats to Dutch (Netherlands) then run the unit tests.

I have solved the issue in my forked branch. The relevant commit is here:
greigs@0745929

I hope you can include these changes in the next release.

Thanks,
Greig

BigQuery splits pages of results into 10000 by default, regardless of MaxResults.
I have modified the code to allow result set pages to be combined when the result size is > 10000.
The commit is here: greigs@fced906

Released April 16, 2015
Added new operators.

https://cloud.google.com/bigquery/release-notes#04162015

for example +9 Time,

new DateTime(2014, 10, 16, 12, 0, 0) => 2014-10-16 21:00:00.000000

new DateTime(2014, 10, 16, 21, 0, 0, DateTimeKind.Utc) => 2014-10-16 21:00:00.000000

Release Note:August 25
https://cloud.google.com/bigquery/release-notes#08252015

Google BigQuery adds UDF support for deeper cloud analytics
http://googlecloudplatform.blogspot.jp/2015/08/Google-BigQuery-adds-UDF-support-for-deeper-cloud-analytics.html

This results in only the first page of results being returned when calling RunAsync instead of Run. The code that handles downloading the pages can be found here:

In reality, due to the possibility of memory issues if there are a large number of pages, control over how to handle paging should be exposed. However, care must be taken to not change the current default behaviour, which is to download all pages.

https://cloud.google.com/blog/big-data/2016/06/bigquery-111-now-with-standard-sql-iam-and-partitioned-tables

sample
http://googledevelopers.blogspot.jp/2014/03/geoip-geolocation-with-google-bigquery.html

    var context = Query.GetContext();
context.From<wikipedia>()
.Where(x => x.contributor_ip != null)
.Select(x => new
{
    clientIpNum =  BqFunc.Integer(BqFunc.ParseIP(x.contributor_ip)),
    classB = BqFunc.Integer(BqFunc.ParseIP(x.contributor_ip) / (256 * 256))
})
.AsSubquery()
.Join(JoinType.InnerEach,context.From<geolite_city_bq_b2b>(),  (a,b) => new { a,b}, x => x.a.classB == x.b.classB)
.Where(x => BqFunc.Between(x.a.clientIpNum , x.b.startIpNum, x.b.endIpNum) && x.b.city != "")
.Select(x => new
{
    c = BqFunc.Count(),
    x.b.city,
    x.b.countryLabel,
    lat = BqFunc.Nth(1, x.b.latitude),
    lng = BqFunc.Nth(1, x.b.longitude)
})
.GroupBy(x => new { x.city, x.countryLabel})
.OrderByDescending(x => x.c)
.ToString()
.Dump();

in LINQPad Driver, now if type is record, does not expand inner column.

JOIN clause
https://developers.google.com/bigquery/query-reference#joins

[[INNER|LEFT OUTER|CROSS] JOIN [EACH] table_2|(subselect2) [[AS] tablealias2]
    ON join_condition_1 [... AND join_condition_N ...]]+

BigQuery supports multiple JOIN operations.

• CROSS JOIN clauses must not contain an ON clause.
• The EACH modifier can't be used in CROSS JOIN clauses

Method Signature?

enum JoinType
{
    Inner,
    InnerEach
    LeftOuter,
    LeftOuterEach
}

Join(rightSource, mergeCondition, aliasSelector) // JoinType.Inner
Join(joinType, rightSource, mergeCondition, aliasSelector)
JoinCross(rightSource, aliasSelector) // CROSS JOIN must not contains mergeCondition

.From()
.Join(ys, (x, y) => x.hoge == y.hoge, (x, y) => new { wiki = x, goog = y })
.Join(zs, (x, y) => x.wiki.hoge == y.hoge, (x, y) => new { x.wiki, x.goog, huga = y })
.GroupBy...

// IEnumerable's JOIN
public static IEnumerable<TResult> Join<TOuter, TInner, TKey, TResult>(this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector, Func<TOuter, TInner, TResult> resultSelector);

// BigQuery?
public static IBigQueryable<TResult> Join<TOuter, TInner, TResult>(this IBigQueryable<TOuter> outer, IBigQueryable<TInner> inner, Func<TOuter, TInner, bool> mergeCondition, Func<TOuter, TInner, TResult> aliasSelector);

https://developers.google.com/bigquery/query-reference#from

[FROM [(FLATTEN(table_name1|(subselect1)] [, table_name2|(subselect2), ...)]

From<T>(params string[] tableName)
From<T>(params ISelectBigQueryable[] tableName)

Table wildcard functions

string TableWildcard.DateRange(string prefix, DateTime from, DateTime to)
string TableWildcard.DateRangeStrict(string prefix, DateTime from, DateTime to)
string TableWildcard.Query(dataset, expr)

alias is needed use join.

*flatten

Breaking the SQL Barrier: Google BigQuery User-Defined Functions
http://googledevelopers.blogspot.jp/2015/08/breaking-sql-barrier-google-bigquery.html

BigQuery User-Defined Functions
https://cloud.google.com/bigquery/user-defined-functions

I'll update soon.

How do I get this using the library? Which is use the count in the select and order by the count

SELECT actor_login, count(*) as Count FROM [github.dotnet]
WHERE type = "PullRequestEvent"
group by actor_login
order by Count desc
limit 100

I tried this but the

var pr = "PullRequestEvent";
var basequery = From<dotnet>().Where(d => d.type == pr)
.Select(e => new { e.actor_login, e.repo_name })
.GroupBy(e => new { e.actor_login, e.repo_name})
.Limit(100)
.RunDry()
.Dump();

The query it output from the linq is

SELECT
  [actor_login],
  [repo_name]
FROM
  [github-data-1163:github.dotnet]
WHERE
  ([type] = 'PullRequestEvent')
GROUP BY
  [actor_login],
  [repo_name]
LIMIT 100

for example ""bigquery-samples:wikipedia_pageviews_s2.201001" =>

[TableName("[bigquery-samples:wikipedia_pageviews_s2.201001]")]
public class _201001
{