Google

Cloud SDK

YouTube

BigQuery

console.cloud.google.com/bigquery

Link

Magic Commands

Magic Command: %sh

%sh เป็นการใช้คำสั่ง shell

%sh ps
%sh ps | grep 'java'

การอ้างถึงพาทต่างๆของ %sh เป็นพาทของ shell

%sh ls -l /

ต่างกับ %fs ที่การอ้างพาท เป็นพาทของ dbfs

%fs ls /

Databricks File System – DBFS

  • DBFS is a layer over a cloud-based object store
  • Files in DBFS are persisted to the object store
  • The lifetime of files in the DBFS are NOT tied to the lifetime of our cluster

Magic Command: Other Languages

%python

print("Hello Python!")
%scala

println("Hello Scala!")
%sql

select "Hello SQL!"
%r

print("Hello R!", quote=FALSE)

Magic Command: %md

%md
%md
### Magic Command: %md

Our favorite Magic Command **%md** allows us to render Markdown in a cell:
* Double click this cell to begin editing it
* Then hit `Esc` to stop editing

# Title One
## Title Two
### Title Three

This is a test of the emergency broadcast system. This is only a test.

This is text with a **bold** word in it.

This is text with an *italicized* word in it.

This is an ordered list
0. once
0. two
0. three

This is an unordered list
* apples
* peaches
* bananas

Links/Embedded HTML: <a href="http://bfy.tw/19zq" target="_blank">What is Markdown?</a>

Images:
![Spark Engines](https://files.training.databricks.com/images/Apache-Spark-Logo_TM_200px.png)

And of course, tables:

| Name  | Age | Sex    |
|-------|-----|--------|
| Tom   | 32  | Male   |
| Mary  | 29  | Female |
| Dick  | 73  | Male   |
| Sally | 55  | Female |

Magic Command: %run

  • You can run a notebook from another notebook by using the Magic Command %run
  • All variables & functions defined in that other notebook will become available in your current notebook
%run "./Includes/Classroom-Setup"

Magic Command: %fs

It is a wrapper around dbutils.fs and it is the Magic Command known as %fs.

The following call is equivalent to the display( dbutils.fs.ls("/mnt/training") ) – there is no real difference between the two.

%fs ls /mnt/training
%fs head /mnt/training/pageviews_by_second.tsv

Python PIP

Check if PIP is Installed

python -m pip --version
pip --version

update pip

pip install --upgrade pip

upgrade package

pip install --upgrade  <package-name>

ตัวอย่างติดตั้ง package camelcase

pip install camelcase

การใช้ Package camelcase · PyPI

import camelcase

c = camelcase.CamelCase()
txt = "hello world"

print(c.hump(txt))           # Hello World
from camelcase import CamelCase
c = CamelCase()
s = 'this is a sentence that needs CamelCasing!'
print (c.hump(s))
# This is a Sentence That Needs CamelCasing!

*ดูจาก output เหมือนจะเป็น PascalCase ?

Find Packages

PyPI · The Python Package Index

Remove a Package

> pip uninstall camelcase

Listing Packages #

> pip list

To list outdated packages, and show the latest version available:

> pip list --outdated
Package    Version Latest Type
---------- ------- ------ -----
Jinja2     3.1.2   3.1.3  wheel
Markdown   3.5.1   3.5.2  wheel
setuptools 65.5.0  69.0.3 wheel

To show details about an installed package:

> python -m pip show Jinja2
Name: Jinja2
Version: 3.1.2
Summary: A very fast and expressive template engine.
Home-page: https://palletsprojects.com/p/jinja/
Author: Armin Ronacher
Author-email: armin.ronacher@active-4.com
License: BSD-3-Clause
Location: c:\project\github3\mkdocs\.venv\lib\site-packages
Requires: MarkupSafe
Required-by: mkdocs, mkdocs-material

Python Variables

Creating Variables

x = 5                # int
y = "John"           # str
z = True             # bool

Case-Sensitive

x = 5
X = 10.0
print(x == X)        # False

Casting

str(obj) แปลงเป็นสตริง
int(s) แปลงเป็นเลขจำนวนเต็ม
float(s)
แปลงเป็นเลขทศนิยม

x = str(3)           # x will be str
y = int(3)           # y will be int
z = float(3)         # z will be float
x = int(1)           # x will be 1
y = int(2.8)         # y will be 2
z = int("3")         # z will be 3
x = float(1)         # x will be 1.0
y = float(2.8)       # y will be 2.8
z = float("3.5")     # z will be 3.5

hex(i) แปลงเป็นเลขฐานสิบหก
oct(i) แปลงเป็นเลขฐานแปด

x = hex(16)          # x will be str 0x10
y = oct(8)           # y will be str 0o10

ord(c) แปลงอักขระเป็นรหัสแอสกี้

x = ord('A')         # x will be int 65

.NET 5 – dotnet new console

สร้างโปรเจ็กส์ด้วย Template แบบ Console Application

dotnet new console -f net5.0 -o Console5

จะได้ไฟล์

  1. Console5\Console5.csproj
  2. Console5\Program.cs

ไฟล์ Console5.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>

</Project>

ไฟล์ Program.cs

using System;

namespace Console5
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");
        }
    }
}

ปัญหา Error – lzma

UserWarning: Could not import the lzma module.

/home/ctlfw/.local/lib/python3.7/site-packages/pandas/compat/__init__.py:120: UserWarning: Could not import the lzma module. Your installed Python is incomplete. Attempting to use lzma compression will result in a RuntimeError.
  warnings.warn(msg)

ทางแก้ติดตั้ง lzma-dev

For ubuntu: sudo apt-get install liblzma-dev

For centos: sudo yum install -y xz-devel

เข้าไปในโฟลเดอร์ที่ใช้ติดตั้ง Python แล้วรัน

Then sudo configure && sudo make && sudo make install

NumPy

ตรวจสอบเวอร์ชันของ numpy

import numpy
print(numpy.version.version)
# 1.19.5
import numpy as np
print(np.__version__)
# 1.19.5
> conda list

Arrays

The array object in NumPy is called ndarray

import numpy as np
arr = np.array([1, 2, 3, 4, 5])
print(arr)
print(type(arr))
# [1 2 3 4 5]
# <class 'numpy.ndarray'>

0-D Arrays

import numpy as np
arr = np.array(5)
print(arr)
print(type(arr))
# 5
# <class 'numpy.ndarray'>

1-D Arrays

import numpy as np
arr = np.array([1,2,3])
print(arr)
print(type(arr))
# [1 2 3]
# <class 'numpy.ndarray'>

2-D Arrays

import numpy as np

arr = np.array([[1, 2, 3], [4, 5, 6]])
print(arr)
print(type(arr))
# [[1 2 3]
#  [4 5 6]]
# <class 'numpy.ndarray'>

3-D Arrays

import numpy as np
arr = np.array([[[1, 2, 3], [4, 5, 6]], [[7, 8, 9], [10, 11, 12]]])
print(arr)
print(type(arr))
# [[[1 2 3]
#   [4 5 6]]
# 
#  [[ 7  8  9]
#   [10 11 12]]]
# <class 'numpy.ndarray'>

Number of Dimensions

import numpy as np
a = np.array(5)
b = np.array([1, 2, 3])
c = np.array([[1, 2, 3], [4, 5, 6]])
d = np.array([[[1, 2, 3], [4, 5, 6]], [[7, 8, 9], [10, 11, 12]]])

print(a.ndim)
print(b.ndim)
print(c.ndim)
print(d.ndim)
# 0
# 1
# 2
# 3

Define the number of dimensions by using the ndmin argument.

import numpy as np
arr = np.array([1, 2, 3, 4], ndmin=5)

print(arr)
print('number of dimensions :', arr.ndim)
# [[[[[1 2 3 4]]]]]
# number of dimensions : 5

NumPy Data Types

NumPy has some extra data types, and refer to data types with one character, like i for integers, u for unsigned integers etc.

  • i – integer
  • b – boolean
  • u – unsigned integer
  • f – float
  • c – complex float
  • m – timedelta
  • M – datetime
  • O – object
  • S – string
  • U – unicode string
  • V – fixed chunk of memory for other type ( void )
import numpy as np
arr = np.array([1, 2, 3, 4])
print(arr.dtype)
# int32
import numpy as np
arr = np.array(['apple', 'banana', 'cherry'])
print(arr.dtype)
# <U6
import numpy as np
arr = np.array([1, 2, 3, 4], dtype='S')
print(arr)
print(arr.dtype)
# [b'1' b'2' b'3' b'4']
# |S1
import numpy as np
arr = np.array([1, 2, 3, 4], dtype='i4')
print(arr)
print(arr.dtype)
# [1 2 3 4]
# int32

Converting Data Type

import numpy as np
arr = np.array([1.1, 2.1, 3.1])
newarr = arr.astype('i') # newarr = arr.astype(int)
print(newarr)
print(newarr.dtype)
# [1 2 3]
# int32
import numpy as np
arr = np.array([1, 0, 3])
newarr = arr.astype(bool)
print(newarr)
print(newarr.dtype)
# [ True False  True]
# bool

Array Copy vs View

The main difference between a copy and a view of an array is that the copy is a new array, and the view is just a view of the original array.

import numpy as np
arr = np.array([1, 2, 3, 4, 5])
x = arr.copy()
arr[0] = 42
print(arr)
print(x)
# [42  2  3  4  5]
# [1 2 3 4 5]
import numpy as np
arr = np.array([1, 2, 3, 4, 5])
x = arr.view()
arr[0] = 42
print(arr)
print(x)
# [42  2  3  4  5]
# [42  2  3  4  5]

Check if Array Owns it’s Data

copies owns the data, and views does not own the data

Every NumPy array has the attribute base that returns None if the array owns the data.

import numpy as np
arr = np.array([1, 2, 3, 4, 5])
x = arr.copy()
y = arr.view()

print(arr.base)
print(x.base)
print(y.base)
# None
# None
# [1 2 3 4 5]

Array Shape

Array Shape

import numpy as np
arr = np.array([[1, 2, 3, 4], [5, 6, 7, 8]])
print(arr.shape)
print(type(arr.shape))
# (2, 4)
# <class 'tuple'>
import numpy as np
arr = np.array([1, 2, 3, 4], ndmin=5)

print(arr)
print('shape of array :', arr.shape)
# [[[[[1 2 3 4]]]]]
# shape of array : (1, 1, 1, 1, 4)

Array Reshaping

Reshape From 1-D to 2-D

import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12])
newarr = arr.reshape(4, 3)
print(newarr)
# [[ 1  2  3]
#  [ 4  5  6]
#  [ 7  8  9]
#  [10 11 12]]

Reshape From 1-D to 3-D

import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12])
newarr = arr.reshape(2, 3, 2)
print(newarr)
# [[[ 1  2]
#   [ 3  4]
#   [ 5  6]]
# 
#  [[ 7  8]
#   [ 9 10]
#   [11 12]]]

Reshape return view

import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6, 7, 8])
print(arr.reshape(2, 4).base)
# [1 2 3 4 5 6 7 8]

Unknown Dimension

You are allowed to have one “unknown” dimension.

import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6, 7, 8])
newarr = arr.reshape(2, 2, -1) # newarr = arr.reshape(2, 2, 2)
print(newarr)
# [[[1 2]
#   [3 4]]
# 
#  [[5 6]
#   [7 8]]]

Flattening the arrays

Flattening array means converting a multidimensional array into a 1D array.

import numpy as np
arr = np.array([[1, 2, 3], [4, 5, 6]])
newarr = arr.reshape(-1)
print(newarr)
# [1 2 3 4 5 6]

Array Iterating

import numpy as np
arr = np.array([1, 2, 3])
for x in arr:
    print(x)
# 1
# 2
# 3
import numpy as np
arr = np.array([[1, 2, 3], [4, 5, 6]])
for x in arr:
    print(x)
# [1 2 3]
# [4 5 6]
import numpy as np
arr = np.array([[1, 2, 3], [4, 5, 6]])
for x in arr:
    for y in x:
        print(y)
# 1
# 2
# 3
# 4
# 5
# 6

nditer() – Iterating Arrays Using nditer()

import numpy as np
arr = np.array([[[1, 2], [3, 4]], [[5, 6], [7, 8]]])
for x in arr:
    print(x)
# [[1 2]
#  [3 4]]
# [[5 6]
#  [7 8]]
import numpy as np
arr = np.array([[[1, 2], [3, 4]], [[5, 6], [7, 8]]])
for x in np.nditer(arr):
    print(x)
# 1
# 2
# 3
# 4
# 5
# 6
# 7
# 8

nditer() – Iterating Array With Different Data Types

import numpy as np
arr = np.array([1, 2, 3])
for x in np.nditer(arr, flags=['buffered'], op_dtypes=['S']):
    print(x)
# b'1'
# b'2'
# b'3'

nditer() – Iterating With Different Step Size

import numpy as np
arr = np.array([[1, 2, 3, 4], [5, 6, 7, 8]])
for x in np.nditer(arr[:, ::2]):
    print(x)
# 1
# 3
# 5
# 7

ndenumerate()

import numpy as np
arr = np.array([1, 2, 3])
for idx, x in np.ndenumerate(arr):
    print(idx, x)
# (0,) 1
# (1,) 2
# (2,) 3
import numpy as np
arr = np.array([[1, 2, 3], [4, 5, 6]])
for idx, x in np.ndenumerate(arr):
    print(idx, x)
# (0, 0) 1
# (0, 1) 2
# (0, 2) 3
# (1, 0) 4
# (1, 1) 5
# (1, 2) 6

NumPy Joining Array

Joining NumPy Arrays

import numpy as np
arr1 = np.array([1, 2, 3])
arr2 = np.array([4, 5, 6])
arr = np.concatenate((arr1, arr2))
print(arr)
# [1 2 3 4 5 6]

Join two 2-D arrays along cols (axis=0)

import numpy as np
arr1 = np.array([[1, 2], [3, 4]])
arr2 = np.array([[5, 6], [7, 8]])
arr = np.concatenate((arr1, arr2), axis=0)
print(arr)
# [[1 2]
#  [3 4]
#  [5 6]
#  [7 8]]

Join two 2-D arrays along rows (axis=1)

import numpy as np
arr1 = np.array([[1, 2], [3, 4]])
arr2 = np.array([[5, 6], [7, 8]])
arr = np.concatenate((arr1, arr2), axis=1)
print(arr)
# [[1 2 5 6]
#  [3 4 7 8]]

stack()

import numpy as np
arr1 = np.array([1, 2, 3])
arr2 = np.array([4, 5, 6])
arr = np.stack((arr1, arr2), axis=0)
print(arr)
# [[1 2 3]
#  [4 5 6]]
import numpy as np
arr1 = np.array([1, 2, 3])
arr2 = np.array([4, 5, 6])
arr = np.stack((arr1, arr2), axis=1)
print(arr)
# [[1 4]
#  [2 5]
#  [3 6]]

hstack()

import numpy as np
arr1 = np.array([1, 2, 3])
arr2 = np.array([4, 5, 6])
arr = np.hstack((arr1, arr2))
print(arr)
# [1 2 3 4 5 6]

vstack()

import numpy as np
arr1 = np.array([1, 2, 3])
arr2 = np.array([4, 5, 6])
arr = np.vstack((arr1, arr2))
print(arr)
# [[1 2 3]
#  [4 5 6]]

dstack()

import numpy as np
arr1 = np.array([1, 2, 3])
arr2 = np.array([4, 5, 6])
arr = np.dstack((arr1, arr2))
print(arr)
# [[[1 4]
#   [2 5]
#   [3 6]]]

Splitting Array

import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6])
newarr = np.array_split(arr, 3)

print(type(newarr))
print(newarr)
# <class 'list'>
# [array([1, 2]), array([3, 4]), array([5, 6])]

print(newarr[0])
print(type(newarr[0]))
# [1 2]
# <class 'numpy.ndarray'>
import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6])
newarr = np.array_split(arr, 4)
print(newarr)
# [array([1, 2]), array([3, 4]), array([5]), array([6])]
import numpy as np
arr = np.array([1, 2, 3, 4, 5, 6])
newarr = np.array_split(arr, 5)
print(newarr)
# [array([1, 2]), array([3]), array([4]), array([5]), array([6])]

Splitting 2-D Arrays

import numpy as np
arr = np.array([[1, 2], [3, 4], [5, 6], [7, 8], [9, 10], [11, 12]])
print(arr)
# [[ 1  2]
#  [ 3  4]
#  [ 5  6]
#  [ 7  8]
#  [ 9 10]
#  [11 12]]

newarr = np.array_split(arr, 3) # newarr = np.array_split(arr, 3, axis=0)
print(newarr[0])
print(newarr[1])
print(newarr[2])
# [[1 2]
#  [3 4]]
# [[5 6]
#  [7 8]]
# [[ 9 10]
#  [11 12]]
import numpy as np
arr = np.array([[1, 2], [3, 4], [5, 6], [7, 8], [9, 10], [11, 12]])
print(arr)
newarr = np.array_split(arr, 3, axis=1)
print(newarr[0])
print(newarr[1])
# [[ 1]
#  [ 3]
#  [ 5]
#  [ 7]
#  [ 9]
#  [11]]
# [[ 2]
#  [ 4]
#  [ 6]
#  [ 8]
#  [10]
#  [12]]
import numpy as np
arr = np.array([[1, 2], [3, 4], [5, 6], [7, 8], [9, 10], [11, 12]])
newarr = np.hsplit(arr, 2)
print(newarr[0])
print(newarr[1])
# [[ 1]
#  [ 3]
#  [ 5]
#  [ 7]
#  [ 9]
#  [11]]
# [[ 2]
#  [ 4]
#  [ 6]
#  [ 8]
#  [10]
#  [12]]




ติดตั้ง SQL Server 2017 บน Ubuntu 18.04

Install SQL Server 2017

1.Import the public repository GPG keys:

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

2.Register the Microsoft SQL Server Ubuntu repository:

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2017.list)"

3.Run the following commands to install SQL Server:

sudo apt-get update
sudo apt-get install -y mssql-server

4.After the package installation finishes, run mssql-conf setup and follow the prompts to set the SA password and choose your edition.

sudo /opt/mssql/bin/mssql-conf setup

5.Once the configuration is done, verify that the service is running:

systemctl status mssql-server --no-pager

Install the SQL Server command-line tools

sudo apt install curl 

1.Import the public repository GPG keys.

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

2.Register the Microsoft Ubuntu repository.

curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

3.Update the sources list and run the installation command with the unixODBC developer package. For more information, see Install the Microsoft ODBC driver for SQL Server (Linux).

sudo apt-get update 
sudo apt-get install mssql-tools unixodbc-dev

4.Optional: Add /opt/mssql-tools/bin/ to your PATH environment variable in a bash shell.

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Connect locally

sqlcmd -S localhost -U SA -P '<YourPassword>'
SELECT Name from sys.Databases
GO

.NET Core ต่อ Database (Blazor)

สร้างโปรเจ็กส์ Blazor

> dotnet new blazorserver -o BlazorApp --no-https

รันโปรเจ็กส์ด้วยคำสั่ง dotnet run

> cd BlazorApp
> dotnet run

หรือ แก้ไขหน้า Page ดูผลการแก้ไขได้เลย

> dotnet watch run

ติดตั้ง System.Data.SqlClient

> dotnet add package System.Data.SqlClient --version 4.8.2

เพิ่ม Config ที่ไฟล์ appsettings.json

{
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft": "Warning",
            "Microsoft.Hosting.Lifetime": "Information"
        }
    },
    "DbConfig": {
        "ServerName": "localhost",
        "DatabaseName": "myDatabase",
        "UserName": "myUsername",
        "Password": "myPassword"
    },
    "AllowedHosts": "*"
}

เพิ่มไฟล์ Data.Student.cs

using System;

namespace BlazorApp.Data
{
    public class Student
    {
        public int id { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public string email { get; set; }
        public string mobile { get; set; }
    }
}

เพิ่มไฟล์ Data.StudentService.cs

using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;

namespace BlazorApp.Data
{
    public class StudentService
    {
        private IConfiguration config;
        public StudentService(IConfiguration configuration)
        {
            config = configuration;
        }

        private string ConnectionString
        {
            get
            {
                string _server = config.GetValue<string>("DbConfig:ServerName");
                string _database = config.GetValue<string>("DbConfig:DatabaseName");
                string _username = config.GetValue<string>("DbConfig:UserName");
                string _password = config.GetValue<string>("DbConfig:Password");
                return ($"Server={_server};Database={_database};User ID={_username};Password={_password};Trusted_Connection=False;MultipleActiveResultSets=true;");
            }
        }

        public async Task<List<Student>> GetStudent()
        {
            List<Student> students = new List<Student>();
            DataTable dt = new DataTable();
            SqlConnection con = new SqlConnection(ConnectionString);
            string sql = @"
SELECT * 
FROM Student
ORDER BY id";
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(dt);
            foreach (DataRow row in dt.Rows)
            {
                Student std = new Student();
                std.id = Convert.ToInt32(row["id"]);
                std.firstname = row["Firstname"] as string;
                std.lastname = row["Lastname"] as string;
                std.email = row["Email"] as string;
                std.mobile = row["mobile"] as string;
                students.Add(std);
            }

            return await Task.FromResult(students);
        }
    } // end class
}

สร้าง Razor Page ชื่อ Pages/StudentList.razor

@page "/student-list"
@using BlazorApp.Data
@inject StudentService stdService

<h1>Student</h1>

@if (students == null)
{
    <div>There is no student</div>
}
else
{
    foreach (Student std in students)
    {
        <div style="padding:15px;border-bottom:solid 1px #0094ff;">
            @std.id | @std.firstname | @std.lastname | @std.email | @std.mobile
        </div>
    }
}

@code {
    private List<Student> students;
    protected override async Task OnInitializedAsync()
    {
        students = await stdService.GetStudent();
    }
}

Register service ใน Startup.cs

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddRazorPages();
            services.AddServerSideBlazor();
            services.AddSingleton<WeatherForecastService>();
            services.AddSingleton<StudentService>();
        }

รันและเรียกไปที่ http://localhost:5000/student-list

หรือแปะไว้หน้า index โดยแก้ไขไฟล์ Pages/index.razor

@page "/"

<h1>Hello, world!</h1>

Welcome to your new app.

<SurveyPrompt Title="How is Blazor working for you?" />

<StudentList />