我怎样才能建立一个前端查询红移数据库(希望使用Rails) [英] How can I build a front end for querying a Redshift database (hopefully with Rails)
问题描述
所以我有一个红移数据库有足够的表,它的感觉值得我的时间来建立一个前端,使查询比刚才输入的SQL命令稍微容易一些。
在理想情况下,我能够通过数据库连接到一个Rails应用程序(因为我有一点经验,使用Rails)来做到这一点。我不知道我怎么会连接远程数据库红移到本地Rails应用程序,虽然,或如何使红移ActiveRecord的工作。
有没有人有任何建议/资源来帮助我开始?我接受其他选项的红移数据库连接到前端,如果有pre制选择比Rails的容易。
#应用程序/模型/ data_warehouse.rb
级数据仓库和LT;的ActiveRecord :: Base的
establish_connectionredshift_staging
#or,如果你想拥有每环境分贝
#establish_connection红移_#{Rails.env}
结束
请注意,我们要连接上5439,而不是默认的5432,所以我指定的端口 另外,我指定一个模式,测试版,这是我们用什么为我们的不稳定集合体,你既可以有每个环境不同的数据库如上所述,或使用不同的模式,包括他们的ActiveRecord的搜索路径
#配置/ database.yml中
redshift_staging:
适配器:PostgreSQL的
编码:UTF8
数据库:DB03
端口:5439
池:5
schema_search_path:'测试版'
用户名:admin
密码:supersecretpassword
主持人:在这里db03.myremotehost.us #your远程主机,可能是从红移管理控制台的AWS网址
###选项2,直接PG连接
级数据仓库<的ActiveRecord :: Base的
attr_accessor:康恩
高清初始化
@conn = PG.connect(
数据库:DB03,
端口:5439,
池:5,
schema_search_path:'测试版',
用户名:管理员,
密码:supersecretpassword,
主持人:db03.myremotehost.us
)
结束
结束
[DEV]主:0>红移=数据仓库
E,[2014-07-17T11:09:17.758957#44535]错误 - :PG :: InsufficientPrivilege:错误:拒绝权限设置参数client_min_messages通知:设置client_min_messages TO通知
(撬)输出错误:#<的ActiveRecord :: StatementInvalid:PG :: InsufficientPrivilege:错误:拒绝权限设置参数client_min_messages通知:设置client_min_messages TO'通知'>
更新:
我最终会与选项1,但使用该适配器现在有多种原因:
<一个href="https://github.com/fiksu/activerecord-redshift-adapter">https://github.com/fiksu/activerecord-redshift-adapter
原因1:ActiveRecord的PostgreSQL的适配器设置client_min_messages 原因2:适配器还尝试设置时区,其红移不允许(<一href="http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html">http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html) 原因3:即使你改变了code在ActiveRecord的前两个错误,你碰上抱怨红移使用PostgreSQL 8.0,在这一点上我转移到适配器其他错误,将重新审视和更新,如果我后来找到更好的东西。
我在我的表重命名为base_aggregate_redshift_tests(注复数)这样的ActiveRecord可以非常容易地进行连接,如果你不能在红移改变你的表名使用set_table方法,我有评论如下
#Gemfile:
创业板activerecord4红移适配器,github上:aamine / activerecord4红移适配器
选项1
#配置/ database.yml中
redshift_staging:
适配器:红移
编码:UTF8
数据库:DB03
端口:5439
池:5
用户名:admin
密码:supersecretpassword
主持人:db03.myremotehost.us
超时:5000
#应用程序/模型/ base_aggregates_redshift_test.rb
#Model为匹配我的表在红移,如果你愿意,你可以set_table像我有评论如下
类BaseAggregatesRedshiftTest&LT;的ActiveRecord :: Base的
establish_connectionredshift_staging
self.table_name =beta.base_aggregates_v2
结束
使用self.table_name
控制台 - 注意它查询正确的表,所以你可以命名你的模型任何你想要
[DEV]主:0&GT;红移= BaseAggregatesRedshiftTest.first
研发,[2014-07-17T15:31:58.678103#43776] DEBUG - :BaseAggregatesRedshiftTest负荷(45.6ms)选择测试版base_aggregates_v2* FROM测试版base_aggregates_v2LIMIT 1。
选项2
#应用程序/模型/ base_aggregates_redshift_test.rb
类BaseAggregatesRedshiftTest&LT;的ActiveRecord :: Base的
set_tablebeta.base_aggregates_v2
的ActiveRecord :: Base.establish_connection(
适配器:红移,
编码:'UTF8',
数据库:'分期',
端口:'5439',
池:'5',
用户名:管理员,
密码:supersecretpassword,
search_schema:'测试版',
主持人:db03.myremotehost.us,
超时:'5000'
)
结束
#in控制台,第一条记录的缩写例子,现在它使用新名称为我的红移表,只是假设我已经得到了纪录base_aggregates_redshift_tests,因为我没有设置TABLE_NAME
[DEV]主:0&GT;红移= BaseAggregatesRedshiftTest.first
研发,[2014-07-17T15:09:39.388918#11537] DEBUG - :BaseAggregatesRedshiftTest负荷(45.3ms)选择base_aggregates_redshift_tests* FROMbase_aggregates_redshift_testsLIMIT 1。
#&LT; BaseAggregatesRedshiftTest:0x007fd8c4a12580&GT; {
:truncated_month =&GT;星期四,2013年1月31日19:00:00 EST -05:00,
:DMA =&GT; 的CityVille,
:GROUP_ID =&GT; 9712338,
:dma_id =&GT; 9999
}
祝你好运@johncorser!
So I have a Redshift database with enough tables that it feels worth my time to build a front end to make querying it a little bit easier than just typing in SQL commands.
Ideally, I'd be able to do this by connecting the database to a Rails app (because I have a bit of experience with Rails). I'm not sure how I'd connect a remote Redshift database to a local Rails application though, or how to make activerecord work with redshift.
Does anyone have any suggestions/resources to help me get started? I'm open to other options to connect the Redshift database to a front end if there are pre-made options easier than Rails.
#app/models/data_warehouse.rb
class DataWarehouse < ActiveRecord::Base
establish_connection "redshift_staging"
#or, if you want to have a db per environment
#establish_connection "redshift_#{Rails.env}"
end
Note that we are connecting on 5439, not the default 5432 so I specify the port Also, I specify a schema, beta, which is what we use for our unstable aggregates, you could either have a different db per environment as mentioned above, or use various schemas and include them in the search path for ActiveRecord
#config/database.yml
redshift_staging:
adapter: postgresql
encoding: utf8
database: db03
port: 5439
pool: 5
schema_search_path: 'beta'
username: admin
password: supersecretpassword
host: db03.myremotehost.us #your remote host here, might be an aws url from Redshift admin console
###OPTION 2, a direct PG Connection
class DataWarehouse < ActiveRecord::Base
attr_accessor :conn
def initialize
@conn = PG.connect(
database: 'db03',
port: 5439,
pool: 5,
schema_search_path: 'beta',
username: 'admin',
password: 'supersecretpassword',
host: 'db03.myremotehost.us'
)
end
end
[DEV] main:0> redshift = DataWarehouse
E, [2014-07-17T11:09:17.758957 #44535] ERROR -- : PG::InsufficientPrivilege: ERROR: permission denied to set parameter "client_min_messages" to "notice" : SET client_min_messages TO 'notice'
(pry) output error: #<ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR: permission denied to set parameter "client_min_messages" to "notice" : SET client_min_messages TO 'notice'>
UPDATE:
I ended up going with option 1, but using this adapter for now for multiple reasons:
https://github.com/fiksu/activerecord-redshift-adapter
Reason 1: ActiveRecord postgresql adapter sets client_min_messages Reason 2: adapter also attempts to set Time Zone, which redshift doesn't allow (http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html) Reason 3: Even if you change the code in ActiveRecord for the first two errors, you run into additional errors that complain that Redshift is using Postgresql 8.0, at that point I moved on to the adapter, will revisit and update if I find something better later.
I renamed my table to base_aggregate_redshift_tests (notice plural) so ActiveRecord was easily able to connect, if you can't change your table names in redshift use the set_table method I have commented out below
#Gemfile:
gem 'activerecord4-redshift-adapter', github: 'aamine/activerecord4-redshift-adapter'
Option 1
#config/database.yml
redshift_staging:
adapter: redshift
encoding: utf8
database: db03
port: 5439
pool: 5
username: admin
password: supersecretpassword
host: db03.myremotehost.us
timeout: 5000
#app/models/base_aggregates_redshift_test.rb
#Model named to match my tables in Redshift, if you want you can set_table like I have commented out below
class BaseAggregatesRedshiftTest < ActiveRecord::Base
establish_connection "redshift_staging"
self.table_name = "beta.base_aggregates_v2"
end
in console using self.table_name -- notice it queries the right table, so you can name your models whatever you want
[DEV] main:0> redshift = BaseAggregatesRedshiftTest.first
D, [2014-07-17T15:31:58.678103 #43776] DEBUG -- : BaseAggregatesRedshiftTest Load (45.6ms) SELECT "beta"."base_aggregates_v2".* FROM "beta"."base_aggregates_v2" LIMIT 1
Option 2
#app/models/base_aggregates_redshift_test.rb
class BaseAggregatesRedshiftTest < ActiveRecord::Base
set_table "beta.base_aggregates_v2"
ActiveRecord::Base.establish_connection(
adapter: 'redshift',
encoding: 'utf8',
database: 'staging',
port: '5439',
pool: '5',
username: 'admin',
password: 'supersecretpassword',
search_schema: 'beta',
host: 'db03.myremotehost.us',
timeout: '5000'
)
end
#in console, abbreviated example of first record, now it's using the new name for my redshift table, just assuming I've got the record at base_aggregates_redshift_tests because I didn't set the table_name
[DEV] main:0> redshift = BaseAggregatesRedshiftTest.first
D, [2014-07-17T15:09:39.388918 #11537] DEBUG -- : BaseAggregatesRedshiftTest Load (45.3ms) SELECT "base_aggregates_redshift_tests".* FROM "base_aggregates_redshift_tests" LIMIT 1
#<BaseAggregatesRedshiftTest:0x007fd8c4a12580> {
:truncated_month => Thu, 31 Jan 2013 19:00:00 EST -05:00,
:dma => "Cityville",
:group_id => 9712338,
:dma_id => 9999
}
Good luck @johncorser!
这篇关于我怎样才能建立一个前端查询红移数据库(希望使用Rails)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!