Postgresql的json类型

最近工作需要使用 sql 语句进行 Postgresql json 类型字段的查询,本文特此记录下一些常用的函数。 Postgresql json 类型简介 postgresql支持两种json数据类型:json和jsonb,而两者唯一的区别在于效率,json是对输入的完整拷贝,使用时再去解析,所以它会保留输入的空格,重复键以及顺序等。而jsonb是解析输入后保存的二进制,它在解析时会删除不必要的空格和重复的键,顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别:json类型存储快,使用慢,jsonb类型存储稍慢,使用较快。 json 类型操作符 我们先介绍 json 和 jsonb 的一些常用通用操作符: 操作符 右操作数类型 描述 示例 结果 -> int 获取JSON数组元素(索引从0开始) select ‘[{“a”:“foo”},{“b”:“bar”},{“c”:“baz”}]’::json->2; {“c”:“baz”} -> text 通过键获取值 select ‘{“a”: {“b”:“foo”}}’::json->‘a’; {“b”:“foo”} -» int 获取JSON数组元素为 text select ‘[1,2,3]’::json-»2; 3 -» text 通过键获取值为text select ‘{“a”:1,“b”:2}’::json-»‘b’; 2 jsonb 独有的操作符 操作符 右操作数类型 描述 示例 结果 @> jsonb 左侧json最上层的值是否包含右边json对象 select ‘{“a”:{“b”:2}}’::jsonb @> ‘{“b”:2}’::jsonb; select ‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2}’::jsonb; f t <@ jsonb 左侧json对象是否包含于右侧json最上层的值内 select ‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonb; t ? text text是否作为左侧Json对象最上层的键 select ‘{“a”:1, “b”:2}’::jsonb ? ‘b’; t ?| text[] text[]中的任一元素是否作为左侧Json对象最上层的键 select ‘{“a”:1, “b”:2, “c”:3}’::jsonb ?| array[‘b’, ‘c’]; t ?& text[] text[]中的所有元素是否作为左侧Json对象最上层的键 select ‘[“a”, “b”]’::jsonb ?& array[‘a’, ‘b’]; t || jsonb 连接两个json对象,组成一个新的json对象 select ‘[“a”, “b”]’::jsonb || ‘[“c”, “d”]’::jsonb; [“a”, “b”, “c”, “d”] - text 删除左侧json对象中键为text的键值对 select ‘{“a”: “b”}’::jsonb - ‘a’; {} - integer 删除数组指定索引处的元素,如果索引值为负数,则从右边计算索引值。如果最上层容器内不是数组,则抛出错误。 select ‘[“a”, “b”]’::jsonb - 1; [“a”] 创建json类型 那我们该如何创建json类型呢,下面介绍一些常见的函数 ...

十二月 24, 2022 · overstarry

使用 APISIX 代理 PostgreSQL 数据库

本文我来讲解如何使用 APISIX 来代理 PostgreSQL 数据库服务。 初试 最开始我以为很简单,我采用了直接在 APISIX DashBoard 上配置了一条路由,配置好后,连接发现无法成功连接,提示连接失败。 经过仔细的思考,发现 PostgreSQL 是使用 TCP 协议的数据库,不能通过简单的 route 进行配置,必须使用 stream-proxy 进行代理,接下来我们来看看怎么样来进行配置。 stream-proxy TCP 是许多流行应用程序和服务的协议,例如 LDAP、MySQL 和 RTMP。UDP(用户数据报协议)是许多流行的非事务性应用程序的协议,例如 DNS、系统日志和 RADIUS。 APISIX 可以动态负载平衡 TCP/UDP 代理。在 Nginx 的世界里,我们把 TCP/UDP proxy 称为 stream proxy,APISIX 就遵循了这个说法。 启用 stream-proxy 要开启 APISIX 流代理需要在 APISIX 配置中开启, APISIX 默认是关闭的。 apisix: stream_proxy: # TCP/UDP proxy tcp: # TCP proxy address list - 9100 - "127.0.0.1:9101" udp: # UDP proxy address list - 9200 - "127.0.0.1:9211" 如果apisix.enable_admin为 true,则 HTTP 和流代理都启用了上述配置。 ...

十二月 17, 2022 · overstarry

DataX数据同步中遇到的问题

最近在使用 DataX 进行 PostgreSQL 和 PostgreSQL 之间的数据同步,在数据同步过程中, 遇到了一个问题,在本文简单记录下问题和相应的解决方案。 问题 在一次数据同步中,DataX执行失败,错误信息如下: 具体错误信息为:com.alibaba.datax.common.exception.DataXException: Code:[DBUtilErrorCode-12], Description:[不支持的数据库类型. 请注意查看 DataX 已经支持的数据库类型以及数据库版本.]. - 您的配 置文件中的列配置信息有误. 因为DataX 不支持数据库读取这种字段类型. 字段名:[country], 字段名称:[1111], 字段Java类型:[java.lang.String]. 请尝试使用数据库函数将其转换datax支持的类型 我的配置如下: { "job": { "setting": { "speed": { "channel": 3 }, "errorLimit": { "record": 0, "percentage": 0.02 } }, "content": [ { "reader": { "name": "postgresqlreader", "parameter": { "username": "xasdas", "password": "xxx", "column": [ "id", "country" ], "connection": [ { "table": [ "xx" ], "jdbcUrl": [ "jdbc:postgresql://xxx:5432/xxxx" ] } ] } }, "writer": { "name": "postgresqlwriter", "parameter": { "username": "xxxx", "password": "x", "column": [ "id", "country" ], "preSql": [ ], "postSql": [ ], "connection": [ { "jdbcUrl": "jdbc:postgresql://xxx:5432/xxxx", "table": [ "xx" ] } ] } } } ] } } 通过检查数据库字段,发现 country 字段是 jsonb 类型,DataX不支持此类型,DataX 的支持列表: ...

十二月 7, 2022 · overstarry

Go时间处理库carbon

我们在日常开发中,常常会对时间进行各种处理,今天我来介绍一个 go 处理时间的库 golang-module/carbon。 Carbon 是一个轻量级、语义化、对开发者友好的 golang 时间处理库,支持链式调用。 安装 go版本大于 1.16 go get -u github.com/golang-module/carbon/v2 版本小于 1.16 go get -u github.com/golang-module/carbon 使用 创建 carbon 实例 carbon.Now() // 获取当前时间 carbon.Now("Asia/Qatar").ToDateTimeString() // 不同时区当前的时间,卡塔尔当前的时间 carbon.Yesterday() // 昨天 carbon.CreateFromTime(22, 9, 13).ToString() carbon.Tomorrow().ToDateString() // 明天 标准库转换 carbon 还可以和标准库 time 进行相互转换。 // 将 标准库 time 转换为 carbon 实例 carbon.Time2Carbon(time.Now()) // carbon 转换为 time.Time 解析字符串 carbon 可以解析字符串,生成carbon 实例 // 将时间字符串转换为 carbon 实例 fmt.Println(carbon.Parse("1998-04-01").ToDateTimeString()) carbon.Parse("tomorrow").ToString() carbon.ParseByFormat("2020|08|05 13|14|15", "Y|m|d H|i|s").ToDateTimeString() // 2020-08-05 13:14:15 carbon.ParseByFormat("It is 2020-08-05 13:14:15", "\\I\\t \\i\\s Y-m-d H:i:s").ToDateTimeString() // 2020-08-05 13:14:15 carbon.ParseByFormat("今天是 2020年08月05日13时14分15秒", "今天是 Y年m月d日H时i分s秒").ToDateTimeString() // 2020-08-05 13:14:15 carbon.ParseByFormat("2020-08-05 13:14:15", "Y-m-d H:i:s", carbon.Tokyo).ToDateTimeString() // 2020-08-05 14:14:15 获取开始时间和结束时间 carbon 可以很方便的获取一个时刻的开始和结束 ...

十二月 3, 2022 · overstarry

Superset简单使用

本文我来讲讲 Superset 的简单使用,并使用 superset 制作一个简单的图表。 Apache Superset 介绍 Apache Superset 是一款现代化的开源大数据工具,也是企业级商业智能 Web 应用,用于数据探索分析和数据可视化。它提供了简单易用的无代码可视化构建器和声称是最先进的 SQL 编辑器,用户可以使用这些工具快速地构建数据仪表盘。 Superset 提供了源码、pypi、Docker 等多种安装方式,其文档称,Superset 目前在许多公司被大规模使用。例如,Superset 在 Airbnb 基于 Kubernetes 的生产环境中运行,为每天查看超过 10 万张图表的 600 多名活跃用户提供服务。 Apache Superset 将 SQL IDE、数据浏览工具、拖拽式仪表板编辑器和插件组合使用,以构建自定义的可视化效果,支持从许多关系数据库和非关系数据库中创建仪表板,这些数据库包括 SQLite、MySQL,以及 Amazon Redshift、Google BigQuery、Snowflake、Oracle 数据库、IBM DB2 和其他各种兼容的数据源,并且可以连接到 Apache Drill 和 Apache Druid。此外,Superset 还适用于云原生场景和 Docker。 安装 我采用官方提供的 helm chart 安装在 Kubernetes 上,安装过程中遇到了一些问题。这里就不过多讲述了,接下来进入本文的正题,如何使用 superset 制作一个简单的图表呢。 简单使用 连接数据源 我们打开 Superset 主界面,点击右上角 settings ,选择 连接数据库选项,进入数据库配置界面。点击右上角的添加数据库,选择相应的数据源,并填写相应的数据库连接配置,superset 内置几种常见的数据源,如果你想添加的数据源类型不再此列,可能需要安装相应的 python package 来解决。 ...

十一月 26, 2022 · overstarry

使用Fly.io部署MinIO对象存储服务

前面的文章讲了Fly.io 的入门使用和如何使用 Fly.io 部署 go 开发的应用程序。今天我们来继续讲解使用 Fly.io 部署 对象存储服务 MinIO. MinIO 是什么我就不过多介绍了, 接下来我们就进入正题,进行 MinIO 服务的部署。 准备 Dockerfile 文件 我们先准备 MinIO docker 部署相关的文件, 我们使用官方的镜像进行部署, Dockerfile 文件内容如下: FROM minio/minio CMD [ "server", "/data", "--console-address", ":9001"] MinIO 的数据文件将存储在 data 目录下,指定 9001 为 web 控制台的端口,如果不指定将会使用随机端口。 初始化 Fly.io 应用 接下来像以前一样使用 flyctl launch 命令进行应用的初始化。 我们照常不使用数据库,不立即部署,生成的配置如下: # fly.toml file generated for twilight-lake-5450 on 2022-11-19T22:34:39+08:00 app = "twilight-lake-5450" kill_signal = "SIGINT" kill_timeout = 5 processes = [] [env] [experimental] allowed_public_ports = [] auto_rollback = true [[services]] http_checks = [] internal_port = 8080 processes = ["app"] protocol = "tcp" script_checks = [] [services.concurrency] hard_limit = 25 soft_limit = 20 type = "connections" [[services.ports]] force_https = true handlers = ["http"] port = 80 [[services.ports]] handlers = ["tls", "http"] port = 443 [[services.tcp_checks]] grace_period = "1s" interval = "15s" restart_limit = 0 timeout = "2s" 如果我们要限制从公网连接 MinIO ,只允许和 MinIO 使用相同网络的应用访问,只要删除 [[services]] 下的内容即可。 ...

十一月 19, 2022 · overstarry

Fly.io部署go应用

前面讲解了 Fly.io 的初步入门使用,本文讲解在没有 docker 环境情况下部署 go 开发的应用。 项目 这里为了演示方便, 简单使用 go 编写一个: package main import ( "fmt" "log" "net/http" ) func main() { http.HandleFunc("/", func(w http.ResponseWriter, r *http.Request) { fmt.Fprintf(w,"hello world") }) log.Println("listening on", 8080) log.Fatal(http.ListenAndServe(":"+"8080", nil)) } 安装 Flyctl 并登录 这个步骤上篇文章已经介绍过来,还不了解如何安装登录的可以参考前面的文章。 配置 Fly 应用程序 进入项目的根目录,执行 flyctl launch,它会根据你输入的配置(主要是应用名,部署区域,是否需要数据库和redis)来生成相应的配置文件,并帮你在远程构建器上打包镜像并部署,如果有 DOCKERFILE 文件就会使用你的进行构建,如果没有就会使用相应的官方文件进行构建镜像. 需要注意的是你的应用需要监听 8080 端口,否则部署时会报错 从日志可以看到打包好的镜像名是 registry.fly.io/cool-grass-2591:deployment-01GHPEDCXBC3K5NAGHNZWKT49H fly.toml 配置 我们来看看 flyctl 帮我们生成的 fly.toml 具体内容: # fly.toml file generated for cool-grass-2591 on 2022-11-13T01:32:35+08:00 app = "cool-grass-2591" kill_signal = "SIGINT" kill_timeout = 5 processes = [] [env] [experimental] allowed_public_ports = [] auto_rollback = true [[services]] http_checks = [] internal_port = 8080 processes = ["app"] protocol = "tcp" script_checks = [] [services.concurrency] hard_limit = 25 soft_limit = 20 type = "connections" [[services.ports]] force_https = true handlers = ["http"] port = 80 [[services.ports]] handlers = ["tls", "http"] port = 443 [[services.tcp_checks]] grace_period = "1s" interval = "15s" restart_limit = 0 timeout = "2s" 主要就是一些常规的服务端口配置等。 ...

十一月 12, 2022 · overstarry

Png压缩工具

最近有一个需求,需要对 png 和 jpg 格式的图片进行压缩,jpg 格式的图片压缩比较简单,难点主要是针对 png 图片,本想继续采用前篇文章的方式将 png 转为 jpg 图片进行压缩的,但我发现部分转换后的图片出现比较大的问题,图片的背景由其它颜色变成了黑色,那看来不能采用原来的方法,得寻找新的方法,通过查阅资料发现了2个压缩 png 图片的工具 pngquant 和 pngcrush,接下来我就来分别介绍这2个工具。 pngquant pngquant是一个命令行实用程序,也是一个用于PNG图像有损压缩的库。这种转换大大减少了文件大小(通常多达70%),并保留了完全的alpha透明度。 下载 可以通过官网提供的 gui 程序或者 命令行程序链接下载 pngquant,也可以通过源代码进行编译,我采用了编译好的命令行程序。 参数 options: --force 覆盖原来输出的图片 --skip-if-larger 仅当原来图片小于原图片时才保留 --output file 输出的文件路径 --ext new.png 为输出文件名设置自定义扩展名。默认情况下使用-or8.png或-fs8.png。 --quality min-max 指示使用达到或超过最高质量pngquant所需的最少颜色。如果转换导致质量低于最低质量,则图像将不会被保存(如果输出到标准输出,将输出 24 位原始图像)并将以状态码 99 退出。pngquant min和max是 0(最差)到 100(完美)范围内的数字 --speed N 从 1(暴力)到 10(最快)的速度/质量权衡。默认值为 3。速度 10 的质量降低 5%,但比默认值快 8 倍。 --nofs 禁用 Floyd-Steinberg dithering --posterize N output lower-precision color (e.g. for ARGB4444 output) --strip remove optional metadata (default on Mac) --verbose 打印状态信息 简单使用 压缩单个 png pngquant –force –verbose –quality=45-85 pngpath ...

十一月 5, 2022 · overstarry

apisix实现nginx proxy_hide_header参数

前言 最近需要使用 apisix 反向代理 oss, 实现通过域名访问相应的资源(由于条件的限制,不能在oss绑定自定义域名),我直接在 apisix dashboard 配置了路由,创建成功后,我通过浏览器访问时,发现资源不能预览会直接下载。 这样的现象让我很奇怪,通过查阅相关资料,发现使用OSS默认域名通过文件URL从浏览器访问图片或者网页文件时,Response Header中会自动加上Content-Disposition:attachment。 即从浏览器访问这些文件时,会以附件形式进行下载。 解决 通过询问同事,得知他是通过 nginx 的 proxy_hide_header 忽略 Content-Disposition 解决的。 那 apisix 该怎么实现呢,我通过自己的不断尝试和提 issue 询问官方人员,得知可以使用 response-rewrite 插件实现这个功能。 接下来我就来介绍下我尝试的过程吧,我最开始是使用 proxy-rewrite 将 Content-Disposition 设置为 inline,具体配置如下: { "uri": "/*", "name": "xx", "host": "xixi.xx.work", "plugins": { "proxy-rewrite": { "headers": { "Content-Disposition": "inline" }, "host": "xx", "regex_uri": [ "/(.*)$", "/xx/${1}" ] } }, "upstream": { "nodes": [ { "host": "xxx.com", "port": 80, "weight": 1 } ], "timeout": { "connect": 6, "send": 6, "read": 6 }, "type": "roundrobin", "scheme": "http", "pass_host": "pass", "keepalive_pool": { "idle_timeout": 60, "requests": 1000, "size": 320 } }, "status": 1 } curl 结果: ...

十一月 5, 2022 · overstarry

前后端使用AES加密传输数据

最近遇到前后端传输数据需要进行加密的需求,本篇文章就分别介绍使用 Node.js 和 go 进行 AES 加密解密的方法, AES 有很多不同的算法,如aes192,aes-128-ecb,aes-256-cbc等,根据不同的密钥长度会使用不同的算法。 加密后的结果有两种表示方法:hex和base64,我们这里使用 hex. golang 使用 golang 实现 aes 加密,我使用标准库的方法实现,我使用的 CBC 模式。 加密 func AesEncrypt(encryptStr string, key []byte, iv string) (string, error) { encryptBytes := []byte(encryptStr) block, err := aes.NewCipher(key) if err != nil { return "", err } blockSize := block.BlockSize() encryptBytes = pkcs5Padding(encryptBytes, blockSize) blockMode := cipher.NewCBCEncrypter(block, []byte(iv)) encrypted := make([]byte, len(encryptBytes)) blockMode.CryptBlocks(encrypted, encryptBytes) return hex.EncodeToString(encrypted), nil } 解密 func AesDecrypt(decryptStr string, key []byte, iv string) (string, error) { decryptBytes, err := hex.DecodeString(decryptStr) if err != nil { return "", err } block, err := aes.NewCipher(key) if err != nil { return "", err } blockMode := cipher.NewCBCDecrypter(block, []byte(iv)) decrypted := make([]byte, len(decryptBytes)) blockMode.CryptBlocks(decrypted, decryptBytes) decrypted = pkcs5UnPadding(decrypted) return string(decrypted), nil } 运行加密解密例子 package main import ( "bytes" "crypto/aes" "crypto/cipher" "encoding/hex" "fmt" ) func AesEncrypt(encryptStr string, key []byte, iv string) (string, error) { encryptBytes := []byte(encryptStr) block, err := aes.NewCipher(key) if err != nil { return "", err } blockSize := block.BlockSize() encryptBytes = pkcs5Padding(encryptBytes, blockSize) blockMode := cipher.NewCBCEncrypter(block, []byte(iv)) encrypted := make([]byte, len(encryptBytes)) blockMode.CryptBlocks(encrypted, encryptBytes) return hex.EncodeToString(encrypted), nil } func AesDecrypt(decryptStr string, key []byte, iv string) (string, error) { decryptBytes, err := hex.DecodeString(decryptStr) if err != nil { return "", err } block, err := aes.NewCipher(key) if err != nil { return "", err } blockMode := cipher.NewCBCDecrypter(block, []byte(iv)) decrypted := make([]byte, len(decryptBytes)) blockMode.CryptBlocks(decrypted, decryptBytes) decrypted = pkcs5UnPadding(decrypted) return string(decrypted), nil } func pkcs5Padding(cipherText []byte, blockSize int) []byte { padding := blockSize - len(cipherText)%blockSize padText := bytes.Repeat([]byte{byte(padding)}, padding) return append(cipherText, padText...) } func pkcs5UnPadding(decrypted []byte) []byte { length := len(decrypted) unPadding := int(decrypted[length-1]) return decrypted[:(length - unPadding)] } func main() { data := "i am test data" key := []byte("1111111111111111") iv := "1111122211111111" encrypt, err := AesEncrypt(data, key, iv) if err != nil { panic(err) return } fmt.Println(encrypt) decrypt, err := AesDecrypt(encrypt, key, iv) if err != nil { panic(err) } fmt.Println(decrypt) } ...

十月 29, 2022 · overstarry