Npy

Input	Output	Alias
✔	✔

Description

The Npy format is designed to load a NumPy array from a .npy file into ClickHouse. The NumPy file format is a binary format used for efficiently storing arrays of numerical data. During import, ClickHouse treats the top level dimension as an array of rows with a single column.

The table below gives the supported Npy data types and their corresponding type in ClickHouse:

Data types matching

Npy data type (INSERT)	ClickHouse data type	Npy data type (SELECT)
i1	Int8	i1
i2	Int16	i2
i4	Int32	i4
i8	Int64	i8
u1, b1	UInt8	u1
u2	UInt16	u2
u4	UInt32	u4
u8	UInt64	u8
f2, f4	Float32	f4
f8	Float64	f8
S, U	String	S
	FixedString	S

Example usage

Saving an array in .npy format using Python

import numpy as np
arr = np.array([[[1],[2],[3]],[[4],[5],[6]]])
np.save('example_array.npy', arr)

Reading a NumPy file in ClickHouse

SELECT *
FROM file('example_array.npy', Npy)

┌─array─────────┐
│ [[1],[2],[3]] │
│ [[4],[5],[6]] │
└───────────────┘

Selecting data

You can select data from a ClickHouse table and save it into a file in the Npy format using the following command with clickhouse-client:

$ clickhouse-client --query="SELECT {column} FROM {some_table} FORMAT Npy" > {filename.npy}

Format settings