redash+mysql_Redash版本更新部署

Redash，make your company data driven，connect to any data source, easily visualize and share your data……

背景当前内部使用的redash版本为 0.10.0+b1774，许多新特性都不支持；

新版本的redash已经支持 query params 和 filter；

最新版本的redash已经支持 LDAP 登陆；

新特性

Query Params

query params

MySQL

SELECT DATE, COUNT(*) FROM mytable WHERE DATE = “{
{date}}”

1SELECT DATE, COUNT(*) FROM mytable WHERE DATE = “{
{date}}”

Filter

filter

MySQL

SELECT action AS “action::filter”, COUNT (0) AS “actions count” FROM events GROUP BY action

1SELECT action AS “action::filter”, COUNT (0) AS “actions count” FROM events GROUP BY action

同时也支持  “action::multi-filter” 的写法。

更新

旧版本的redash采用 docker 方式安装，官方并无提供相对应的文档，数据库的迁移存在困难：Docker

If you’re using Docker to run Redash, don’t use the upgrade script, but rather update the Docker image you’re using

Docker 的安装方式并不是官方的推荐方法， From Github#1079：Because there is no documented process for running migrations (and upgrade) for Docker, we don’t recommend it “officially” for usage and assuming that those who still want to use Docker will figure it out.

You should compare the list of migrations between the old image and your current one to see which ones you need to run.

How to run them depends on your Docker setup. Do you use Docker compose?

But in general it’s something along the lines of: PYTHONPATH=. python migrations/ from /opt/redash/current for each migration file.

And once this process will be automated, we will make Docker the recommended deployment method and have it run on container startup…

尝试手动更新并未成功，最后放弃；选择重新部署新版本的redash实例。

部署

Redash 稳定版本为 2.0，并未实现对 LDAP 的支持。From Github#633，LDAP特性的支持在 2017-08-10 进行了Merge，因此考虑安装最新版本的Redash v3.0.0+b。

构建Docker镜像，Dockerfile：

Dockerfile

ZSH

FROM redash/redash:3.0.0.b2998

RUN pip install ldap3

1

2

3FROMredash/redash:3.0.0.b2998

RUNpipinstallldap3

ZSH

docker build -t redash:redash2_ldap .

1dockerbuild-tredash:redash2_ldap.

采用docker-compose启动，docker-compose.yml：

docker-compose.yml

YAML

# This is an example configuration for Docker Compose. Make sure to atleast update

# the cookie secret & postgres database password.

#

# Some other recommendations:

# 1. To persist Postgres data, assign it a volume host location.

# 2. Split the worker service to adhoc workers and scheduled queries workers.

version: ‘2’

services:

server:

image: redash:redash2_ldap

command: server

ports:

– “25001:5000”

environment:

PYTHONUNBUFFERED: 0

REDASH_LOG_LEVEL: “INFO”

REDASH_REDIS_URL: “redis://172.31.0.1:6379/0”

REDASH_DATABASE_URL: “postgresql://redash:password@172.31.0.1/redash2”

REDASH_CELERY_BROKER: “amqp://rabbit:password@172.31.0.1/redash2”

REDASH_CELERY_BACKEND: “redis://172.31.0.1:6379/0”

REDASH_QUERY_RESULTS_CLEANUP_MAX_AGE: 1

REDASH_JOB_EXPIRY_TIME: 3600

REDASH_HOST: “http://redash.chenjiehua.me/”

REDASH_DATE_FORMAT: “YYYY-MM-DD”

REDASH_STATSD_HOST: “172.31.0.1”

REDASH_COOKIE_SECRET: “cookie_secret”

REDASH_MAIL_SERVER: “172.31.0.1”

REDASH_MAIL_DEFAULT_SENDER: “”

REDASH_WEB_WORKERS: 4

REDASH_PASSWORD_LOGIN_ENABLED: “false”

REDASH_LDAP_CUSTOM_USERNAME_PROMPT: “LDAP Username”

REDASH_LDAP_LOGIN_ENABLED: “true”

REDASH_LDAP_URL: “ldaps://172.31.0.1:636”

REDASH_LDAP_BIND_DN: “uid=admin,ou=system”

REDASH_LDAP_BIND_DN_PASSWORD: “ladp_bind_password”

REDASH_LDAP_DISPLAY_NAME_KEY: “uid”

REDASH_LDAP_EMAIL_KEY: “mail”

REDASH_LDAP_SEARCH_TEMPLATE: “(uid=%(username)s)”

REDASH_SEARCH_DN: “ou=users,dc=test,dc=com”

worker:

image: redash:redash2_ldap

command: scheduler

environment:

PYTHONUNBUFFERED: 0

REDASH_LOG_LEVEL: “INFO”

REDASH_REDIS_URL: “redis://172.31.0.1:6379/0”

REDASH_DATABASE_URL: “postgresql://redash:password@172.31.0.1/redash2”

REDASH_CELERY_BROKER: “amqp://rabbit:password@172.31.0.1/redash2”

REDASH_CELERY_BACKEND: “redis://172.31.0.1:6379/0”

REDASH_QUERY_RESULTS_CLEANUP_MAX_AGE: 1

REDASH_JOB_EXPIRY_TIME: 3600

REDASH_DATE_FORMAT: “YYYY-MM-DD”

REDASH_STATSD_HOST: “172.31.0.1”

REDASH_MAIL_SERVER: “172.31.0.1”

QUEUES: “queries,scheduled_queries,celery”

WORKERS_COUNT: 2

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57# This is an example configuration for Docker Compose. Make sure to atleast update

# the cookie secret & postgres database password.

#

# Some other recommendations:

# 1. To persist Postgres data, assign it a volume host location.

# 2. Split the worker service to adhoc workers and scheduled queries workers.

version: ‘2’

services:

server:

image: redash:redash2_ldap

command: server

ports:

-“25001:5000”

environment:

PYTHONUNBUFFERED: 0

REDASH_LOG_LEVEL: “INFO”

REDASH_REDIS_URL: “redis://172.31.0.1:6379/0”

REDASH_DATABASE_URL: “postgresql://redash:password@172.31.0.1/redash2”

REDASH_CELERY_BROKER: “amqp://rabbit:password@172.31.0.1/redash2”

REDASH_CELERY_BACKEND: “redis://172.31.0.1:6379/0”

REDASH_QUERY_RESULTS_CLEANUP_MAX_AGE: 1

REDASH_JOB_EXPIRY_TIME: 3600

REDASH_HOST: “http://redash.chenjiehua.me/”

REDASH_DATE_FORMAT: “YYYY-MM-DD”

REDASH_STATSD_HOST: “172.31.0.1”

REDASH_COOKIE_SECRET: “cookie_secret”

REDASH_MAIL_SERVER: “172.31.0.1”

REDASH_MAIL_DEFAULT_SENDER: “”

REDASH_WEB_WORKERS: 4

REDASH_PASSWORD_LOGIN_ENABLED: “false”

REDASH_LDAP_CUSTOM_USERNAME_PROMPT: “LDAP Username”

REDASH_LDAP_LOGIN_ENABLED: “true”

REDASH_LDAP_URL: “ldaps://172.31.0.1:636”

REDASH_LDAP_BIND_DN: “uid=admin,ou=system”

REDASH_LDAP_BIND_DN_PASSWORD: “ladp_bind_password”

REDASH_LDAP_DISPLAY_NAME_KEY: “uid”

REDASH_LDAP_EMAIL_KEY: “mail”

REDASH_LDAP_SEARCH_TEMPLATE: “(uid=%(username)s)”

REDASH_SEARCH_DN: “ou=users,dc=test,dc=com”

worker:

image: redash:redash2_ldap

command: scheduler

environment:

PYTHONUNBUFFERED: 0

REDASH_LOG_LEVEL: “INFO”

REDASH_REDIS_URL: “redis://172.31.0.1:6379/0”

REDASH_DATABASE_URL: “postgresql://redash:password@172.31.0.1/redash2”

REDASH_CELERY_BROKER: “amqp://rabbit:password@172.31.0.1/redash2”

REDASH_CELERY_BACKEND: “redis://172.31.0.1:6379/0”

REDASH_QUERY_RESULTS_CLEANUP_MAX_AGE: 1

REDASH_JOB_EXPIRY_TIME: 3600

REDASH_DATE_FORMAT: “YYYY-MM-DD”

REDASH_STATSD_HOST: “172.31.0.1”

REDASH_MAIL_SERVER: “172.31.0.1”

QUEUES: “queries,scheduled_queries,celery”

WORKERS_COUNT: 2

ZSH

# 初始化数据库

docker-compose -f docker-compose.yml run –rm server create_db

# 启动

docker-compose -f docker-compose.yml up

1

2

3

4# 初始化数据库

docker-compose-fdocker-compose.ymlrun–rmservercreate_db

# 启动

docker-compose-fdocker-compose.ymlup

至此，在本地测试环境 Docker 17.06.2-ce + OpenLDAP 上测试完成，一切正常运行。

线上部署

线上部署时遇到的问题：LDAP服务器连接失败。

问题1：LDAP连接失败采用 plain 模式连接LDAP服务器，配置为：REDASH_LDAP_URL: “ldap://172.31.0.1:389″；

采用 ssl 模式连接LDAP服务器，配置为：REDASH_LDAP_URL: “ldaps://172.31.0.1:636″；

报错：

Python

[2017-09-18 06:59:08,823][PID:17][ERROR][redash] Exception on /ldap/login [POST]

Traceback (most recent call last):

File “/usr/local/lib/python2.7/dist-packages/flask/app.py”, line 1988, in wsgi_app

response = self.full_dispatch_request()

File “/usr/local/lib/python2.7/dist-packages/flask/app.py”, line 1641, in full_dispatch_request

rv = self.handle_user_exception(e)

File “/usr/local/lib/python2.7/dist-packages/flask_restful/__init__.py”, line 271, in error_router

return original_handler(e)

File “/usr/local/lib/python2.7/dist-packages/flask/app.py”, line 1544, in handle_user_exception

reraise(exc_type, exc_value, tb)

File “/usr/local/lib/python2.7/dist-packages/flask/app.py”, line 1639, in full_dispatch_request

rv = self.dispatch_request()

File “/usr/local/lib/python2.7/dist-packages/flask/app.py”, line 1625, in dispatch_request

return self.view_functions[rule.endpoint](req.view_args)

File “/app/redash/authentication/ldap_auth.py”, line 36, in login

user = auth_ldap_user(request.form[’email’], request.form[‘password’])

File “/app/redash/authentication/ldap_auth.py”, line 54, in auth_ldap_user

conn = Connection(server, settings.LDAP_BIND_DN, password=settings.LDAP_BIND_DN_PASSWORD, authentication=SIMPLE, auto_bind=True)

File “/home/redash/.local/lib/python2.7/site-packages/ldap3/core/connection.py”, line 326, in __init__

self.bind(read_server_info=True)

File “/home/redash/.local/lib/python2.7/site-packages/ldap3/core/connection.py”, line 530, in bind

response = self.post_send_single_response(self.send(‘bindRequest’, request, controls))

File “/home/redash/.local/lib/python2.7/site-packages/ldap3/strategy/sync.py”, line 124, in post_send_single_response

responses, result = self.get_response(message_id)

File “/home/redash/.local/lib/python2.7/site-packages/ldap3/strategy/base.py”, line 342, in get_response

raise LDAPSessionTerminatedByServerError(self.connection.last_error)

LDAPSessionTerminatedByServerError: session terminated by server

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27[2017-09-1806:59:08,823][PID:17][ERROR][redash]Exceptionon/ldap/login[POST]

Traceback(mostrecentcalllast):

File”/usr/local/lib/python2.7/dist-packages/flask/app.py”,line1988,inwsgi_app

response=self.full_dispatch_request()

File”/usr/local/lib/python2.7/dist-packages/flask/app.py”,line1641,infull_dispatch_request

rv=self.handle_user_exception(e)

File”/usr/local/lib/python2.7/dist-packages/flask_restful/__init__.py”,line271,inerror_router

returnoriginal_handler(e)

File”/usr/local/lib/python2.7/dist-packages/flask/app.py”,line1544,inhandle_user_exception

reraise(exc_type,exc_value,tb)

File”/usr/local/lib/python2.7/dist-packages/flask/app.py”,line1639,infull_dispatch_request

rv=self.dispatch_request()

File”/usr/local/lib/python2.7/dist-packages/flask/app.py”,line1625,indispatch_request

returnself.view_functions[rule.endpoint](req.view_args)

File”/app/redash/authentication/ldap_auth.py”,line36,inlogin

user=auth_ldap_user(request.form[’email’],request.form[‘password’])

File”/app/redash/authentication/ldap_auth.py”,line54,inauth_ldap_user

conn=Connection(server,settings.LDAP_BIND_DN,password=settings.LDAP_BIND_DN_PASSWORD,authentication=SIMPLE,auto_bind=True)

File”/home/redash/.local/lib/python2.7/site-packages/ldap3/core/connection.py”,line326,in__init__

self.bind(read_server_info=True)

File”/home/redash/.local/lib/python2.7/site-packages/ldap3/core/connection.py”,line530,inbind

response=self.post_send_single_response(self.send(‘bindRequest’,request,controls))

File”/home/redash/.local/lib/python2.7/site-packages/ldap3/strategy/sync.py”,line124,inpost_send_single_response

responses,result=self.get_response(message_id)

File”/home/redash/.local/lib/python2.7/site-packages/ldap3/strategy/base.py”,line342,inget_response

raiseLDAPSessionTerminatedByServerError(self.connection.last_error)

LDAPSessionTerminatedByServerError:sessionterminatedbyserver

解决方案：对 ldap3 依赖的库 pyasn1 进行降版本(别问我是怎么找到这个解决方案的……)

ldap3 的 requirements.txt 中 pyasn1>=0.1.8，默认安装最新版的 pyasn1==0.3.5，与服务器的LDAP不兼容导致(我猜的 ……)

修改Dockerfile：

Dockerfile

YAML

FROM redash/redash:3.0.0.b2998

RUN pip install pyasn1==0.1.9 ldap3

1

2

3FROMredash/redash:3.0.0.b2998

RUNpipinstallpyasn1==0.1.9ldap3

故障重现：

ldap_test.py

Python

#!/usr/bin/env python

from ldap3 import Server, Connection, SIMPLE

def main():

host = “ldap://172.31.0.1:389”

bind_dn = “uid=admin,ou=system”

password = “ladp_bind_password”

search = “ou=users,dc=test,dc=com”

template = “(uid=%(username)s)”

name_key = “uid”

email_key = “mail”

username = “chenjiehua”

server = Server(host)

print server

conn = Connection(server, bind_dn, password=password, authentication=SIMPLE, auto_bind=True)

print conn

conn.search(search, template % {“username”: username}, attributes=[name_key, email_key])

print conn.entries

if __name__ == “__main__”:

main()

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23#!/usr/bin/env python

fromldap3importServer,Connection,SIMPLE

defmain():

host=”ldap://172.31.0.1:389″

bind_dn=”uid=admin,ou=system”

password=”ladp_bind_password”

search=”ou=users,dc=test,dc=com”

template=”(uid=%(username)s)”

name_key=”uid”

email_key=”mail”

username=”chenjiehua”

server=Server(host)

printserver

conn=Connection(server,bind_dn,password=password,authentication=SIMPLE,auto_bind=True)

printconn

conn.search(search,template%{“username”:username},attributes=[name_key,email_key])

printconn.entries

if__name__==”__main__”:

main()

问题2：redash ldap跳转链接bug

登录界面的LDAP跳转链接为：http://localhost:25000/ldap_auth/login，而真实链接为：http://localhost:25000/ldap/login

解决方案1：nginx rewrite

nginx

INI

location /ldap_auth/login {

rewrite ^/ldap_auth/login$ /ldap/login last;

}

1

2

3location/ldap_auth/login{

rewrite^/ldap_auth/login$/ldap/loginlast;

}

解决方案2：关闭默认登录，强制采用LDAP，登录时自动跳转到正确的ldap登录链接

docker-compose.yml

YAML

REDASH_PASSWORD_LOGIN_ENABLED: “false”

REDASH_LDAP_LOGIN_ENABLED: “true”

1

2REDASH_PASSWORD_LOGIN_ENABLED: “false”

REDASH_LDAP_LOGIN_ENABLED: “true”