BigQuery with .NET Core

Here is a sample respository ready to be injected to a ASP.NET Core application.

   public class SelfViewRepository : ISelfViewRepository
    {
        private readonly string _projectId;
        private readonly GoogleCredential _gcpCredential;

        public SelfViewRepository(string projectId, string credentialFile)
        {
            _projectId = projectId;
            _gcpCredential = GoogleCredential.FromFile(credentialFile);
        }

        public async Task<BigQueryResults> GetData(string query)
        {
            BigQueryClient client = BigQueryClient.Create(_projectId, _gcpCredential);

            BigQueryJob job = client.CreateQueryJob(
                sql: query,
                parameters: null,
                // options: new QueryOptions {UseQueryCache = false});
                options: new QueryOptions { });
            var data = await job.GetQueryResultsAsync();
            // Wait for the job to complete.
            job.PollUntilCompleted();
            // Display the results
            return data;
        }
    }

#googlecloud #bigquery #aspnetcore

Advertisements

JSON Type with MySQL & EF Core

As of MySQL 5.7.8, it has support for Json type. Setting the column type to json would do the job.

  [Column(TypeName = "json")]
  public string Settings{ get; set; }

Or with fluent api:

       modelBuilder.Entity<Blog>(eb =>
        {
            eb.Property(b => b.Settings).HasColumnType("json");
        });

If you send an invalid JSON, MySQL will throw an error of “Invalid JSON text”.
Details on usage are in the official documentation:
https://dev.mysql.com/doc/refman/5.7/en/json.html

In the case of custom objects, Pomelo has a feature as well: https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/14

A nive tutorial from scotch.io:
https://scotch.io/tutorials/working-with-json-in-mysql

#json #mysql #mysqlJson #efcore

Google translate in google sheets

https://support.google.com/docs/answer/3093331?hl=en&dark=1

Gif from: https://jakemiller.net/translate-in-google-sheets/

#GoogleSheets #GoogleTranslate

gcloud basics cheat sheet

GCloud Cheat Sheet:

App Deploy

  • gcloud app deploy ~/my_app/app.yaml
    [Resource]

List Versions

  • gcloud app versions list
  • JSON: gcloud app versions list --format json

List services

Delete services:

gcloud app services delete
[Resource]

List versions

  • “`gcloud app versions list“
  • Powershellgcloud app versions list --format json | ConvertFrom-Json
    [Resource]

Delete Versions

  • gcloud app versions delete

Read logs / tail

  • Read: gcloud app logs read
    [Resource]
  • Tail: gcloud app logs tail
  • Tail: gcloud app logs read --limit 10 --service=default
  • Tail: gcloud app logs read --version=v1
    [Resource]

Deploy google function with node

gcloud functions deploy pplusFunction --runtime nodejs8 --trigger-http

Split Traffic

To send all traffic to ‘v2’ of service ‘s1’, run:
gcloud app services set-traffic s1 –splits v2=1
To split traffic evenly between ‘v1’ and ‘v2’ of service ‘s1’, run:
gcloud app services set-traffic s1 –splits v2=.5,v1=.5

Google Cloud Build Trigger: ASP.NET Core and Angular


steps:
# run npm install for Angular
- name: 'gcr.io/cloud-builders/npm'
  args: ['install']
  dir: 'MyProject.Web/ClientApp'
# build Angular for production
- name: 'gcr.io/cloud-builders/npm'
  args: ['run', 'build','--','--prod']
  dir: 'MyProject.Web/ClientApp'
# publish asp.net core solution  
- name: microsoft/dotnet:2.2-sdk
  args: ['dotnet', 'publish','-c','Release']
# deploy the webapi to the AppEngine
- name: gcr.io/cloud-builders/gcloud
  args: ['app', 'deploy', './MyProject.Web/bin/Release/netcoreapp2.2/publish/app.yaml','--version','staging']
timeout: 660s

Other examples:
https://stackoverflow.com/questions/52725046/google-cloud-builder-how-to-trigger-build-configuration-in-a-subdirectory

#GoogleCloud #gcp #BuildTrigger

Compiling vs Transpiling

A question that I heard recently: Compiling vs Transpiling 

It seems that Wikipedia has enough information as well: “A source-to-source compiler, transcompiler or transpiler is a type of compiler that takes the source code of a program written in one programming language as its input and produces the equivalent source code in another programming language. A source-to-source compiler translates between programming languages that operate at approximately the same level of abstraction, while a traditional compiler translates from a higher level programming language to a lower level programming language.”

Here is the part that NativeScript has in its tooling section: https://docs.nativescript.org/tooling/transpilers

Well, considering how Typescript plays an important role nowadays, transpiling will be a term that we hear a lot.

#typescript #angular #nativescript

SQL and sending emails with Powershell

Run SQL Command

Invoke-Sqlcmd -Query "SELECT COUNT(*) AS Count FROM Table" -ConnectionString "YourConnectionString"

Invoke commands in a script file and save the output in a text file

Invoke-Sqlcmd -InputFile "C:\ScriptFolder\TestSqlCmd.sql" | Out-File -FilePath "C:\ScriptFolder\TestSqlCmd.rpt"

Invoke sql script and write the results into CSV file.

Invoke-Sqlcmd -InputFile "C:\Users\Mehmet\Desktop\Users.sql" -ConnectionString "connection-string" | export-csv "C:\Users\Mehmet\Desktop\SqlResults.csv" 

Invoke sql script and write the results into JSON file.

Invoke-Sqlcmd -InputFile "C:\Users\Mehmet\Desktop\Users.sql" -ConnectionString "connection-string" | ConvertTo-Json | export-csv "C:\Users\Mehmet\Desktop\SqlResults.csv" 

Invoke a script and pass in variable values from a string

C:\> $StringArray = "MYVAR1='String1'", "MYVAR2='String2'"
C:\> Invoke-Sqlcmd -Query "SELECT `$(MYVAR1) AS Var1, `$(MYVAR2) AS Var2" -Variable $StringArray

Send Mail

$PSEmailServer = "smtp-mail-server"
$mypassword = ConvertTo-SecureString "your-password" -AsPlainText -Force
$mycredential = New-Object System.Management.Automation.PSCredential ("your-username", $secpasswd)

Send-MailMessage -From "from@mehmet.es" -To "to@mehmet.es" -Subject "Test email" -Body "This is a test email" -Credential $mycredential

A scenario: Retrieve data from a database and send it as a CSV file.

$CsvFileName = "./Users.csv"
$SqlScriptFile = "Users.sql"
$ConnectionString = "your-connection-string"
$SmtpAddress = "your-smtp"
$UserName = "username"
$Password = "password"
$FromEmail = "from"
$ToEmail = "to"
$EmailSubject = "your-subject"
$EmailBody = "hi there"

$PSEmailServer = $SmtpAddress
$mypassword = ConvertTo-SecureString $Password -AsPlainText -Force
$mycredential = New-Object System.Management.Automation.PSCredential ($UserName, $mypassword)

# Run sql command and create the file
Invoke-Sqlcmd -InputFile $SqlScriptFile  -ConnectionString $ConnectionString  | export-csv $CsvFileName 

# Send the file 
Send-MailMessage -From $FromEmail -To $ToEmail -Subject $EmailSubject -Body $EmailBody -Credential $mycredential -Attachments $CsvFileName

# Remove the file
Remove-Item $CsvFileName

"DONE!"

Note: Invoke-Sqlcmd does not work on Azure WebJobs, it is not installed.

#powershell #powershell-send-email #powershell-sql-commands #powershell-sql-to-csv