DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Optimizing Pgbench for CockroachDB Part 2
  • Optimizing Pgbench for CockroachDB Part 1
  • Using CockroachDB Workloads With Kerberos
  • Getting Started With Windows Containers

Trending

  • A Modern Stack for Building Scalable Systems
  • How to Configure and Customize the Go SDK for Azure Cosmos DB
  • Transforming AI-Driven Data Analytics with DeepSeek: A New Era of Intelligent Insights
  • Building Enterprise-Ready Landing Zones: Beyond the Initial Setup
  1. DZone
  2. Software Design and Architecture
  3. Cloud Architecture
  4. Exploring CockroachDB With Microsoft PowerShell

Exploring CockroachDB With Microsoft PowerShell

In this installment of our series, we are going to venture out into the world of .NET through a scripting language out of Microsoft called PowerShell.

By 
Artem Ervits user avatar
Artem Ervits
DZone Core CORE ·
Jan. 25, 22 · Tutorial
Likes (4)
Comment
Save
Tweet
Share
4.5K Views

Join the DZone community and get the full member experience.

Join For Free
My familiarity with .NET is quite minimal but I do have an extensive background in PowerShell scripting, albeit going years back. Pardon me for being a bit rusty. I've always loved PowerShell when I was working on the Microsoft platform, it allows for interactive and object oriented approach working with databases. Scripting admin tasks for DBAs on Windows was always a challenge for me until PowerShell came into the picture. I had to maintain many database servers and PowerShell became my best friend. Today, I will show you how PowerShell can become your best friend working with CockroachDB!

Note: The title is a bit misleading as you will see this tutorial is more about exploring PowerShell from the console rather than Jupyter Notebook but I do make my best effort to emphasize what does and does not work today in Jupyter when it comes to PowerShell and CockroachDB. I've burned many hours trying to find a workaround but I was not able to make the Postgres driver for .NET work with Jupyter Notebook.

This post is the next installment in the series of tutorials on CockroachDB and Docker Compose. You can find the older posts here:


  1. CockroachDB and Docker Compose.
  2. CockroachDB, Minio and Docker Compose.
  3. CDC with CockroachDB and Minio.
  4. CDC with CockroachDB and Hadoop Ozone.
  5. Exploring CockroachDB with Jupyter Notebook and Python.
  6. Exploring CockroachDB with Jupyter Notebook and R.
  7. Exploring CockroachDB with Jupyter Notebook and Microsoft Powershell.
  • Information on CockroachDB can be found here.
  • Information on Jupyter Notebook can be found here.
  • Information on Microsoft Powershell can be found here.

PowerShell is pre-installed on Windows platform and it is now available on OSX and Linux. I originally attempted to build this tutorial on OSX but I was not able to get Npgsql, a Postgresql .Net driver to work. Instead, I will continue using a Jupyter Notebook container with Ubuntu Linux as my PowerShell platform. Microsoft has made a PowerShell kernel for Jupyter available and we will be using this image throughout the tutorial.

DISCLAIMER: This tutorial is a bit of a "click bait". As you will see further into the post, I was unable to make Npgsql driver to load into the Jupyter userspace. I believe it is due to an open bug, as of the time of writing, that prevents me from completing the tutorial entirely in the Jupyter Notebook. 

The code I will demonstrate, does indeed work in powershell console. I filed an issue with the kernel author for my situation as I cannot explain why it works in the console even though the aforemention bug states loading assemblies pre-installed from NuGet is currently not supported. Needless to say I may be going down the wrong rabbit hole. On with the tutorial..

Install PowerShell

I am still using my old docker-compose script with subtle changes

 
version: '3.7'

services:

 crdb:
   image: cockroachdb/cockroach:v19.2.4
   container_name: crdb
   ports:
     - "26257:26257"
     - "8080:8080"
   command: start-single-node --insecure
   volumes:
     - ${PWD}/cockroach-data/crdb:/cockroach/cockroach-data:rw

jupyter:
   image: jaykul/powershell-notebook-base
   container_name: jupyter
   environment:
     - GRANT_SUDO=yes
   ports:
     - "8888:8888"
   volumes:
     - $PWD:/home/jovyan/work


Even though we are not going to rely on the Jupyter Notebook for the entirety of the post, we're going to use its OS to install PowerShell console.

Attach to the container as root:

 
docker exec --user root -it jupyter bash


The image is based on Ubuntu 16.04 and your steps may very based on the version and flavor of the OS:

 
  mkdir -p /tmp/powershell && cd /tmp/powershell && \
  sudo apt-get update && apt-get install -y apt-transport-https && \
  wget -q https://packages.microsoft.com/config/ubuntu/16.04/packages-microsoft-prod.deb && \
  sudo dpkg -i packages-microsoft-prod.deb && \
  sudo apt update && sudo apt install powershell


Explore Microsoft PowerShell

Attach to the container as regular user:

 
docker exec -it jupyter bash


Typing pwsh at the shell prompt will drop you into the PowerShell console.

 
PowerShell 7.0.0
Copyright (c) Microsoft Corporation. All rights reserved.

https://aka.ms/powershell
Type 'help' to get help.

PS /home/jovyan>


Get-Help is the most obvious starting point in PowerShell if you're unfamiliar. Here's the abbreviated output of that command.

 
...
EXAMPLES:
      Save-Help              : Download help files from the Internet and saves
                               them on a file share.
      Update-Help            : Downloads and installs help files from the
                               Internet or a file share.
      Get-Help Get-Process   : Displays help about the Get-Process cmdlet.
      Get-Help Get-Process -Online
                             : Opens online help for the Get-Process cmdlet.
      Help Get-Process       : Displays help about Get-Process one page at a time.
      Get-Process -?         : Displays help about the Get-Process cmdlet.
      Get-Help About_Modules : Displays help about PowerShell modules.
      Get-Help remoting      : Searches the help topics for the word "remoting."
...


I see Get-Process -? command and find it interesting.

 
PS /Users/artem> Get-Process -?

NAME
    Get-Process

SYNTAX
    Get-Process [[-Name] <string[]>] [-Module] [-FileVersionInfo] [<CommonParameters>]

    Get-Process [[-Name] <string[]>] -IncludeUserName [<CommonParameters>]

    Get-Process -Id <int[]> [-Module] [-FileVersionInfo] [<CommonParameters>]

    Get-Process -Id <int[]> -IncludeUserName [<CommonParameters>]

    Get-Process -InputObject <Process[]> [-Module] [-FileVersionInfo] [<CommonParameters>]

    Get-Process -InputObject <Process[]> -IncludeUserName [<CommonParameters>]

ALIASES
    gps


If you were executing the following command on the same node as CockroachDB service, you could find the `CockroachDB process like so:

 
PS /Users/artem> gps -Name cockroach

 NPM(K)    PM(M)      WS(M)     CPU(s)      Id  SI ProcessName
 ------    -----      -----     ------      --  -- -----------
      0     0.00     211.80       7.50   64538 …45 cockroach


Install Npgsql Postgres Driver for .Net

Npgsql is a .Net driver for Postgresql and we will leverage this library while working with CockroachDB. The recommended way to install this library is with NuGet Package Manager for .Net. Luckily, we can programmatically install Npgsql from NuGet from within PowerShell.

List available modules, specifically, we're looking for Npgsql driver loaded.

 
PS /home/jovyan> Get-Module -ListAvailable

    Directory: /opt/microsoft/powershell/7/Modules

ModuleType Version    PreRelease Name                                PSEdition ExportedCommands
---------- -------    ---------- ----                                --------- ----------------
Manifest   1.2.5                 Microsoft.PowerShell.Archive        Desk      {Compress-Archive, Expand-…
Manifest   7.0.0.0               Microsoft.PowerShell.Host           Core      {Start-Transcript, Stop-Tr…
Manifest   7.0.0.0               Microsoft.PowerShell.Management     Core      {Add-Content, Clear-Conten…
Manifest   7.0.0.0               Microsoft.PowerShell.Security       Core      {Get-Credential, Get-Execu…
Manifest   7.0.0.0               Microsoft.PowerShell.Utility        Core      {Export-Alias, Get-Alias, …
Script     1.4.6                 PackageManagement                   Desk      {Find-Package, Get-Package…
Script     2.2.3                 PowerShellGet                       Desk      {Find-Command, Find-DSCRes…
Script     2.0.5                 PSDesiredStateConfiguration         Core      {Configuration, New-DscChe…
Script     2.0.0                 PSReadLine                          Desk      {Get-PSReadLineKeyHandler,…
Binary     2.0.3                 ThreadJob                           Desk      Start-ThreadJob


Obviously the Npgsql library is not available. Let's list available repos, specifically, we're looking for Nuget where Npgsql is available:

 
PS /home/jovyan> Get-PackageSource

Name                             ProviderName     IsTrusted  Location
----                             ------------     ---------  --------
PSGallery                        PowerShellGet    False      https://www.powershe…


If unavailable, register NuGet repo:

 
Register-PackageSource -Name MyNuGet -Location https://www.nuget.org/api/v2 -ProviderName NuGet


Search for the package after the repo is added:

 
PS /home/jovyan> Find-Package Npgsql

Name                           Version          Source           Summary
----                           -------          ------           -------
Npgsql                         4.1.3.1          MyNuGet          Npgsql is the open source .NET data prov…


Install Npgsql from the newly added repo, -Scope controls the location of the installed package:

 
PS /home/jovyan> Install-Package Npgsql -Force -SkipDependencies -Scope CurrentUser
                                                                                                           Name                           Version          Source           Summary                                   ----                           -------          ------           -------                                   Npgsql                         4.1.3.1          MyNuGet          Npgsql is the open source .NET data prov…
PS /home/jovyan>


Verify Npgsql is installed:

 
PS /home/jovyan> Get-Package Npgsql

Name                           Version          Source                           ProviderName
----                           -------          ------                           ------------
Npgsql                         4.1.3.1          /home/jovyan/.local/share/Packa… NuGet


Load Sample Workload into CockroachDB

 
cockroach workload init movr


Since I'm using a container, command will looke slightly differently:

 
docker exec -it crdb ./cockroach workload init movr
I200311 16:21:22.748581 1 workload/workloadsql/dataload.go:135  imported users (0s, 50 rows)
I200311 16:21:22.760437 1 workload/workloadsql/dataload.go:135  imported vehicles (0s, 15 rows)
I200311 16:21:22.789719 1 workload/workloadsql/dataload.go:135  imported rides (0s, 500 rows)
I200311 16:21:22.820200 1 workload/workloadsql/dataload.go:135  imported vehicle_location_histories (0s, 1000 rows)
I200311 16:21:22.851894 1 workload/workloadsql/dataload.go:135  imported promo_codes (0s, 1000 rows)


Set up a user for our instance:

 
docker exec -it crdb ./cockroach sql --insecure


 
CREATE USER IF NOT EXISTS maxroach;
GRANT ALL ON DATABASE movr TO maxroach;
GRANT ALL ON TABLE movr.* TO maxroach;
\q


Verify maxroach has sufficient priveleges:

 
docker exec -it crdb ./cockroach sql --insecure --user maxroach --database movr


 
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v19.2.4 (x86_64-unknown-linux-gnu, built 2020/02/06 21:55:19, go1.12.12) (same version as client)
# Cluster ID: 28d1b970-83fa-4727-a588-78c0aa5f74fb
#
# Enter \? for a brief introduction.
#
maxroach@:26257/movr> SELECT * FROM rides LIMIT 1;
                   id                  |   city    | vehicle_city |               rider_id               |              vehicle_id              |         start_address          |         end_address         |        start_time         |         end_time          | revenue
+--------------------------------------+-----------+--------------+--------------------------------------+--------------------------------------+--------------------------------+-----------------------------+---------------------------+---------------------------+---------+
  ab020c49-ba5e-4800-8000-00000000014e | amsterdam | amsterdam    | c28f5c28-f5c2-4000-8000-000000000026 | aaaaaaaa-aaaa-4800-8000-00000000000a | 1905 Christopher Locks Apt. 77 | 66037 Belinda Plaza Apt. 93 | 2018-12-13 03:04:05+00:00 | 2018-12-14 08:04:05+00:00 |   77.00
(1 row)

Time: 5.0038ms

maxroach@:26257/movr>


At this point we can start working with CockroachDB from within the PowerShell console.

Going back to the jupyter container, to quickly drop into the PowerShell console while attaching to the container:

 
docker exec -it jupyter pwsh


 
12:36 $ docker exec -it jupyter pwsh
PowerShell 7.0.0
Copyright (c) Microsoft Corporation. All rights reserved.

https://aka.ms/powershell
Type 'help' to get help.

PS /home/jovyan>


We now need to import the Npgsql library into the PowerShell session, but first we need to find it.

The equivalent of ls in PowerShell is Get-ChildItem but ls works just as well. The NuGet packages get installed into the '.local' directory so drilling down into the folder structure, we find the package.

 
PS /home/jovyan> Get-ChildItem ./.local/share/PackageManagement/NuGet/Packages/


    Directory: /home/jovyan/.local/share/PackageManagement/NuGet/Packages

Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
d----           3/11/2020  4:19 PM                Npgsql.4.1.3.1

PS /home/jovyan>


Load Npgsql Package as an Assembly Into PowerShell

I found four different ways to load an assembly but sadly, none of them had worked in Jupyter Notebook. First three work fine in the pwsh console. The last one is a workaround I found but due to my limited knowledge of .Net, I cannot get it to work. In theory, it should work.

 
PS /home/jovyan> Import-Module '/home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll'


The same can be achieved with the following:

 
PS /home/jovyan> Add-Type -Path '/home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll'


And another approach, instead of relying on PowerShell registering the package management capabilities, we fetch the Npgsql binary manually, expanding it and trying to load it.

 
# https://books.google.com/books?id=yxiUDwAAQBAJ&pg=PA138&lpg=PA138&dq=powershell+npgsql&source=bl&ots=MCjIQZIpC6&sig=ACfU3U0YPc74WsKicn02hW5VelSTEFPVKQ&hl=en&sa=X&ved=2ahUKEwi8p9GV0I3oAhXFc98KHZrQCPwQ6AEwB3oECAsQAQ#v=onepage&q=powershell%20npgsql&f=false
Invoke-WebRequest -Uri https://www.nuget.org/api/v2/package/Npgsql/4.1.3.1 -OutFile .\postgres.zip
Expand-Archive .\postgres.zip
Add-Type -Path /home/jovyan/.local/postgres/lib/netstandard2.1/Npgsql.dll -ReferencedAssemblies "/usr/src/jupyter-powershell/System.*.dll"


Yet another approach to load the NuGet library into memory and write it out as a raw assembly. Sadly, it didn't work in the pwsh or the Jupyter Notebook.

 
PS /home/jovyan> $zip = [System.IO.Compression.ZipFile]::Open((Get-Package Npgsql).Source,"Read")
PS /home/jovyan> $memStream = [System.IO.MemoryStream]::new()
PS /home/jovyan> $reader = [System.IO.StreamReader]($zip.entries[2]).Open()
PS /home/jovyan> $reader.BaseStream.CopyTo($memStream)
PS /home/jovyan> [byte[]]$rawAssembly = $memStream.ToArray()
PS /home/jovyan> $reader.Close()
PS /home/jovyan> $zip.dispose()
PS /home/jovyan> [System.Reflection.Assembly]::Load($rawAssembly)
MethodInvocationException: Exception calling "Load" with "1" argument(s): "Bad IL format."
PS /home/jovyan>


Finally, after executing Import-Module or Add-Type, you should have the assembly loaded, quick way to confirm is

 
PS /home/jovyan> [System.AppDomain]::CurrentDomain.GetAssemblies() | Select-String "Npgsql"
Npgsql, Version=4.1.3.1, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7

PS /home/jovyan>


Connect to CockroachDB with PowerShell

 
# Creating a new connection
$connection = [Npgsql.NpgsqlConnection]::new('Host=crdb;Port=26257;Username=maxroach;Password="";Database=movr')
$connection.Open()
$command = [Npgsql.NpgsqlCommand]::new('SELECT * from rides limit 10', $connection)

# https://www.npgsql.org/doc/faq.html
# Exception calling "GetValue" with "1" argument(s): "The field 'city' has a
# type currently unknown to Npgsql (OID 25). You can retrieve it as a string by
# marking it as unknown, please see the FAQ."

# This is only necessary if there are data types in your database that are not known to Npgsql
#$command.AllResultTypesAreUnknown = true;
$reader = $command.ExecuteReader();

$count = 0;
while ($reader.Read())
{
    $reader.GetType().Name
    $reader.GetDataTypeName(0)
    Write-Host $($reader.GetValue(0), $reader.GetValue(1))
    $count++
}

Write-Host "Total records: $($count)";
$connection.CloseAsync()


After you write that into the console and execute, you will get:

 
...
PS /home/jovyan> $reader = $command.ExecuteReader();
PS /home/jovyan>
PS /home/jovyan> $count = 0;
PS /home/jovyan> while ($reader.Read())
>> {
>>     $reader.GetType().Name
>>     $reader.GetDataTypeName(0)
>>     Write-Host $($reader.GetValue(0), $reader.GetValue(1))
>>     $count++
>> }
NpgsqlDataReader
uuid
ab020c49-ba5e-4800-8000-00000000014e amsterdam
NpgsqlDataReader
uuid
ab851eb8-51eb-4800-8000-00000000014f amsterdam
NpgsqlDataReader
uuid
ac083126-e978-4800-8000-000000000150 amsterdam
NpgsqlDataReader
uuid
ac8b4395-8106-4800-8000-000000000151 amsterdam
NpgsqlDataReader
uuid
ad0e5604-1893-4800-8000-000000000152 amsterdam
NpgsqlDataReader
uuid
ad916872-b020-4800-8000-000000000153 amsterdam
NpgsqlDataReader
uuid
ae147ae1-47ae-4800-8000-000000000154 amsterdam
NpgsqlDataReader
uuid
ae978d4f-df3b-4800-8000-000000000155 amsterdam
NpgsqlDataReader
uuid
af1a9fbe-76c8-4800-8000-000000000156 amsterdam
NpgsqlDataReader
uuid
af9db22d-0e56-4800-8000-000000000157 amsterdam
PS /home/jovyan> Write-Host "Total records: $($count)";
Total records: 10
PS /home/jovyan> $connection.CloseAsync()

Id IsCompleted Status
-- ----------- ------
1  True        RanToCompletion


Explore CockroachDB From the PowerShell Console a Bit More

Let's exit out of the container and start from scratch:

 
Add-Type -Path '/home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll'
$connection = [Npgsql.NpgsqlConnection]::new('Host=crdb;Port=26257;Username=maxroach;Password="";Database=movr')
$connection.Open()
$command = [Npgsql.NpgsqlCommand]::new('SHOW CREATE TABLE vehicles', $connection)


Now that we have $command object, let's explore what available methods are available to us.

Type $command. and hit tab.

 
PS /home/jovyan> $command.
AllResultTypesAreUnknown   UnknownResultTypeList      ExecuteScalar
CommandText                UpdatedRowSource           ExecuteScalarAsync
CommandTimeout             Cancel                     GetHashCode
CommandType                Clone                      GetLifetimeService
Connection                 CreateParameter            GetType
Container                  Dispose                    InitializeLifetimeService
DesignTimeVisible          DisposeAsync               Prepare
IsPrepared                 Equals                     PrepareAsync
Parameters                 ExecuteNonQuery            ToString
Site                       ExecuteNonQueryAsync       Unprepare
Statements                 ExecuteReader              Disposed
Transaction                ExecuteReaderAsync


You can quickly see how PowerShell can easily expose available functions to user. This is why I've been using PowerShell back in my SQL Server DBA days extensively. I will leave it to you to explore this further. Let's see what else we can do.

 
$reader = $command.ExecuteReader();


We now have a reader, let's try the same $reader.

 
PS /home/jovyan> $reader.
Depth                         GetDateTime                   GetStreamAsync
FieldCount                    GetDecimal                    GetString
HasRows                       GetDouble                     GetTextReader
IsClosed                      GetEnumerator                 GetTextReaderAsync
IsOnRow                       GetFieldType                  GetTimeSpan
RecordsAffected               GetFieldValue                 GetTimeStamp
Statements                    GetFieldValueAsync            GetType
VisibleFieldCount             GetFloat                      GetValue
Close                         GetGuid                       GetValues
CloseAsync                    GetHashCode                   InitializeLifetimeService
Dispose                       GetInt16                      IsDBNull
DisposeAsync                  GetInt32                      IsDBNullAsync
Equals                        GetInt64                      NextResult
GetBoolean                    GetInterval                   NextResultAsync
GetByte                       GetLifetimeService            Read
GetBytes                      GetName                       ReadAsync
GetChar                       GetOrdinal                    ToString
GetChars                      GetPostgresType               Item
GetColumnSchema               GetProviderSpecificFieldType  ReaderClosed
GetData                       GetProviderSpecificValue      Where
GetDataTypeName               GetProviderSpecificValues     ForEach
GetDataTypeOID                GetSchemaTable
GetDate                       GetStream


Let's select a method for good measure:

 
PS /home/jovyan> $reader.HasRows
True
PS /home/jovyan> $reader.FieldCount
2
PS /home/jovyan> $reader.Statements

SQL             : SHOW CREATE TABLE vehicles
StatementType   : Select
Rows            : 0
OID             : 0
InputParameters : {}

PS /home/jovyan> $reader.GetColumnSchema()
AllowDBNull              :
BaseCatalogName          : movr
BaseColumnName           : table_name
BaseSchemaName           :
BaseServerName           : crdb
BaseTableName            :
ColumnName               : table_name
ColumnOrdinal            : 0
ColumnSize               :
IsAutoIncrement          :
IsKey                    :
IsLong                   : False
IsReadOnly               : True
IsUnique                 :
NumericPrecision         :
NumericScale             :
UdtAssemblyQualifiedName :
DataType                 : System.String
DataTypeName             : text
PostgresType             : text
TypeOID                  : 25
TableOID                 : 0
ColumnAttributeNumber    : 0
DefaultValue             :
NpgsqlDbType             : Text
IsAliased                : False
IsExpression             : False
IsHidden                 : False
IsIdentity               : False

AllowDBNull              :
BaseCatalogName          : movr
BaseColumnName           : create_statement
BaseSchemaName           :
BaseServerName           : crdb
BaseTableName            :
ColumnName               : create_statement
ColumnOrdinal            : 1
ColumnSize               :
IsAutoIncrement          :
IsKey                    :
IsLong                   : False
IsReadOnly               : True
IsUnique                 :
NumericPrecision         :
NumericScale             :
UdtAssemblyQualifiedName :
DataType                 : System.String
DataTypeName             : text
PostgresType             : text
TypeOID                  : 25
TableOID                 : 0
ColumnAttributeNumber    : 0
DefaultValue             :
NpgsqlDbType             : Text
IsAliased                : False
IsExpression             : False
IsHidden                 : False
IsIdentity               : False

PS /home/jovyan> $reader.GetSchemaTable()

ColumnName               : table_name
ColumnOrdinal            : 0
ColumnSize               : -1
NumericPrecision         : 0
NumericScale             : 0
IsUnique                 : False
IsKey                    : False
BaseServerName           :
BaseCatalogName          : movr
BaseColumnName           : table_name
BaseSchemaName           :
BaseTableName            :
DataType                 : System.String
AllowDBNull              :
ProviderType             : 19
IsAliased                : False
IsExpression             : False
IsIdentity               : False
IsAutoIncrement          : False
IsRowVersion             : False
IsHidden                 : False
IsLong                   : False
IsReadOnly               :
ProviderSpecificDataType :
DataTypeName             : text

ColumnName               : create_statement
ColumnOrdinal            : 1
ColumnSize               : -1
NumericPrecision         : 0
NumericScale             : 0
IsUnique                 : False
IsKey                    : False
BaseServerName           :
BaseCatalogName          : movr
BaseColumnName           : create_statement
BaseSchemaName           :
BaseTableName            :
DataType                 : System.String
AllowDBNull              :
ProviderType             : 19
IsAliased                : False
IsExpression             : False
IsIdentity               : False
IsAutoIncrement          : False
IsRowVersion             : False
IsHidden                 : False
IsLong                   : False
IsReadOnly               :
ProviderSpecificDataType :
DataTypeName             : text


We can quickly see how PowerShell allows us to quickly interact with our database. It would be the same experience had the Npgsql assembly would not fail to load in the Jupyter Notebook. Finally, let me show you the output of my code running in the Jupyter Notebook in case you're still interested. Below is the Notebook in MarkDown format for your interest.

 
# List available modules, specifically, we're looking for Npgsql driver loaded.
Get-Module -ListAvailable


 
Directory: /usr/src/jupyter-powershell/Modules


ModuleType Version    Name                                ExportedCommands
---------- -------    ----                                ----------------
Manifest   1.1.0.0    Microsoft.PowerShell.Archive        {Compress-Archive, Expand-Archive}
Manifest   3.0.0.0    Microsoft.PowerShell.Host           {Start-Transcript, Stop-Transcript}
Manifest   3.1.0.0    Microsoft.PowerShell.Management     {Add-Content, Clear-Content, Clear-ItemProperty, Join-Path...}
Manifest   3.0.0.0    Microsoft.PowerShell.Security       {Get-Credential, Get-ExecutionPolicy, Set-ExecutionPolicy, ...
Manifest   3.1.0.0    Microsoft.PowerShell.Utility        {Format-List, Format-Custom, Format-Table, Format-Wide...}
Script     1.1.7.0    PackageManagement                   {Find-Package, Get-Package, Get-PackageProvider, Get-Packag...
Script     1.6.0      PowerShellGet                       {Install-Module, Find-Module, Save-Module, Update-Module...}
Script     0.0        PSDesiredStateConfiguration         {Generate-VersionInfo, Set-PSMetaConfigDocInsProcessedBefor...
Script     1.2        PSReadLine                          {Get-PSReadlineKeyHandler, Set-PSReadlineKeyHandler, Remove...


 
# List available repos, specifically, we're looking for Nuget where Npgsql is available
Get-PackageSource


 
Name                             ProviderName     IsTrusted  Location
----                             ------------     ---------  --------
MyNuGet                          NuGet            False      https://www.nuget.org/api/v2
PSGallery                        PowerShellGet    False      https://www.powershellgallery.com/api/v2


 
# If unavailable, register Nuget repo
Register-PackageSource -Name MyNuGet -Location https://www.nuget.org/api/v2 -ProviderName NuGet


 
# Search for the package after repo is added.
Find-Package Npgsql


 
Name                           Version          Source           Summary
----                           -------          ------           -------
Npgsql                         4.1.3.1          MyNuGet          Npgsql is the open source .NET data provider for Pos...


 
# Install Npgsql from the newly added repo, -Scope controls the location of the installed package
# -Scope CurrentUser means the package will be installed into the .local directory of the user's home.
# leaving scope out will place the package into /usr/local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1
Install-Package Npgsql -Force -SkipDependencies -Scope CurrentUser


 
# Verify Npgsql is installed
Get-Package Npgsql


 
Name                           Version          Source                           ProviderName
----                           -------          ------                           ------------
Npgsql                         4.1.3.1          /home/jovyan/.local/share/Pac... NuGet


 
# (Optional) Get the Strong Name of the assembly for reference, path of the assembly depends on the installation method
[System.Reflection.AssemblyName]::GetAssemblyName('/home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll').FullName


 
Npgsql, Version=4.1.3.1, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7


 
# (Optional) Get the name of the assembly and version
[System.Reflection.AssemblyName]::GetAssemblyName('/home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll')


 
Version        Name
-------        ----
4.1.3.1        Npgsql


 
# Import the Npgsql library for use ((Currently doesn't work for Jupyter, works in powershell console)
Import-Module '/home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll'


 
Import-Module : Could not load file or assembly 'netstandard, Version=2.1.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51'. The system cannot find the file specified.
^M



At line:2 char:1
+ Import-Module '/home/jovyan/.local/share/PackageManagement/NuGet/Pack ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

CategoryInfo          : NotSpecified: (:) [Import-Module], FileNotFoundException

FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.PowerShell.Commands.ImportModuleCommand


 
Add-Type -Path /home/jovyan/.local/share/PackageManagement/NuGet/Packages/Npgsql.4.1.3.1/lib/netstandard2.1/Npgsql.dll


 
# List current assemblies, notice the assembly appears in the list
[System.AppDomain]::CurrentDomain.GetAssemblies() | Select-String "Npgsql"


 
Npgsql, Version=4.1.3.1, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7


 
# doesn't work yet, this is helpful https://stackoverflow.com/questions/34674761/how-to-import-the-npgsql-module
$connection = [Npgsql.NpgsqlConnection]::new('Host=crdb;Port=26257;Username=maxroach;Password="";Database=movr')
$connection.Open()
$command = [Npgsql.NpgsqlCommand]::new('SELECT * from rides limit 10', $connection)


 
 : Unable to find type [Npgsql.NpgsqlConnection].^M



At line:2 char:15
+ $connection = [Npgsql.NpgsqlConnection]::new('Host=crdb;Port=26257;Us ...
+               ~~~~~~~~~~~~~~~~~~~~~~~~~

CategoryInfo          : InvalidOperation: (Npgsql.NpgsqlConnection:TypeName) [], ParentContainsErrorRecordException

FullyQualifiedErrorId : TypeNotFound


Finally, here's a slightly abbreviated Jupyter notebook. I also want to make a commitment that once I hear back about the issue I was having, I will update this post with a workaround. Hope you enjoyed this post, please comment below.

Code sample

That's all! 

PowerShell CockroachDB jupyter notebook Docker (software) Console (video game CLI)

Published at DZone with permission of Artem Ervits. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Optimizing Pgbench for CockroachDB Part 2
  • Optimizing Pgbench for CockroachDB Part 1
  • Using CockroachDB Workloads With Kerberos
  • Getting Started With Windows Containers

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!