Hi, for the first question,you don't provide any detail for analysis, please send me your query diagnostic package which includes your metadata, query, and logs.
For the second question, I am not sure at the moment. ------------------------ With warm regard Xiaoxiang Yu On Thu, Nov 2, 2023 at 10:33 AM Nam Đỗ Duy <na...@vnpay.vn.invalid> wrote: > Thank you Xiaoxiang for the guideline. Will definitely read it carefully. > Kindly help the following questions: > > 1. Computed column > > I created a “computed column” and add it to dimensions (among other > dimensions) > > When I use query to select the computed column it returned error > > 2. Datatype optimization: will you think that the int be better than string > for key join columns? > > Please advise > > > On Wed, 1 Nov 2023 at 17:32 Xiaoxiang Yu <x...@apache.org> wrote: > > > Yes, that is almost correct. > > > > If you have a lot of complex queries, and you want to using Kylin 5 to > > accelerate them, the recommended steps of mine are as follows: > > > > 1. You analyse all queries and collect all join relation/pattern. > > 2. You create Models for each specific join relation/pattern, with the > join > > relation you find in above step. > > 3. You analyse and collect dimensions and measures from all queries, and > > add them to the corresponding Model. > > 4. You build segments of all Models with proper data range. > > 5. You turned off the pushdown switch, and sent all queries to Kylin. If > > there are some queries which failed, fix them. > > Here are some common situations. > > 5.1 Join relation/pattern is not matched > > 5.2 If the join relation is matched, the Model might not contain > every > > column that your query needs, please check kylin.query.log with keyword ' > > unmatched'. > > 6. (Optional) If you find some of your queries do not exactly match with > > your Index(your query on [colA, colB], but your index contains more > columns > > than colA and colB), you can add some aggregate groups(or smaller Table > > Index) to optimize the query performance. > > > > > > > > ------------------------ > > With warm regard > > Xiaoxiang Yu > > > > > > > > On Wed, Nov 1, 2023 at 5:57 PM Nam Đỗ Duy <na...@vnpay.vn.invalid> > wrote: > > > > > Thank you Xiaoxiang, I nearly got to the point. > > > > > > So can I interpret that: 1 model equal (~) to a set of Joins of > > (Dim/Fact) > > > table, that is to say we need to create several models according to > > > multiple kinds of joins queries? > > > > > > Best regards > > > > > > On Wed, Nov 1, 2023 at 4:50 PM Xiaoxiang Yu <x...@apache.org> wrote: > > > > > >> Have you ever tried to analyse the reason why your query can not hit > > >> Model 'sample_ssb'? > > >> It is because the join relation of your query is not suitable for the > > >> join relation/pattern of Model 'sample_ssb'. > > >> > > >> Your query used a join relation/pattern like: A inner join B. > > >> But the Model 'sample_ssb' used a join relation/pattern like : A inner > > >> join B inner join C. > > >> > > >> If you are familiar with the definition of Inner join, you may know > that > > >> the > > >> relation/pattern 'A inner join B inner join C' will have a chance > > >> to lose some rows when compared to pattern 'A inner join B'. > > >> So the Model 'sample_ssb' will be excluded to serve your query. > > >> > > >> That is to say, you need to create a new model that is similar to > Model > > >> 'sample_ssb', > > >> but with additional tables removed. > > >> > > >> > > >> > > >> ------------------------ > > >> With warm regard > > >> Xiaoxiang Yu > > >> > > >> > > >> > > >> On Wed, Nov 1, 2023 at 5:21 PM Nam Đỗ Duy <na...@vnpay.vn.invalid> > > wrote: > > >> > > >>> Hi Xiaoxiang, > > >>> > > >>> Thank you very much > > >>> > > >>> I have clearer picture of Kylin already thanks to your explanation. > > >>> > > >>> Now back to the sample project of SSB in attached photo, when I run > > this > > >>> query with push_down option OFF, why the OLAP error appears, and in > > such > > >>> case, how to create a new cube for this query? > > >>> > > >>> [image: image.png] > > >>> > > >>> On Wed, Nov 1, 2023 at 3:49 PM Xiaoxiang Yu <x...@apache.org> wrote: > > >>> > > >>>> Here is some of my explanation and it may not be perfect. > > >>>> Segment in Kylin is part of model/cube pre-computed data, in most > > >>>> cases, divided by date column. > > >>>> > > >>>> Here is some difference between Segment and Snapshot. > > >>>> Segment, whose source data comes from one fact table joins some > > dimension > > >>>> tables with 'specific date range', is 'precomputed', and will > > accelerate > > >>>> complex query. > > >>>> Snapshot, whose source data comes from one specific dimension table > > without > > >>>> specific date range, is "not precomputed", and can join with > segments > > >>>> at runtime . > > >>>> > > >>>> - https://kylin.apache.org/5.0/docs/snapshot/snapshot_management > > >>>> - > > >>>> > > > https://kylin.apache.org/5.0/docs/modeling/load_data/segment_operation_settings/intro > > >>>> > > >>>> ------------------------ > > >>>> With warm regard > > >>>> Xiaoxiang Yu > > >>>> > > >>>> > > >>>> > > >>>> On Wed, Nov 1, 2023 at 3:53 PM Nam Đỗ Duy <na...@vnpay.vn> wrote: > > >>>> > > >>>>> Thank you again, very smart of you to automatically select cube > for a > > >>>>> certain query. Sorry If I ask too much: Is the concept of Segment > in > > Kylin > > >>>>> model similar to Slice-and-Dice concept of Cube, what is the > > different > > >>>>> between Kylin Segment and Kylin Snapshot? > > >>>>> > > >>>>> PS. I sent you the log files for your help in investigating why my > > >>>>> cube has not been used. > > >>>>> > > >>>>> On Wed, Nov 1, 2023 at 2:36 PM Xiaoxiang Yu <x...@apache.org> > wrote: > > >>>>> > > >>>>>> I guess there is a misunderstanding from your sentences. > > >>>>>> > > >>>>>> -- 'I need to select Cube from a combo box below the query window' > > >>>>>> It is not right to use 'need', that combo box is for some specific > > >>>>>> cases(for example, Kylin did not choose a cube which is the most > > >>>>>> efficient), not the most cases. > > >>>>>> In most cases(both for Kylin 4 and Kylin 5), you don't need to > > select > > >>>>>> a Cube in the combo box, Kylin will do the choice for you. > > >>>>>> > > >>>>>> ------------------------ > > >>>>>> With warm regard > > >>>>>> Xiaoxiang Yu > > >>>>>> > > >>>>>> > > >>>>>> > > >>>>>> On Wed, Nov 1, 2023 at 3:24 PM Nam Đỗ Duy <na...@vnpay.vn.invalid > > > > >>>>>> wrote: > > >>>>>> > > >>>>>>> Hi Xiaoxiang, sorry if I made you confused (Anyway, it is just a > > >>>>>>> question of a beginner) > > >>>>>>> > > >>>>>>> "obviously" means "clearly" > > >>>>>>> > > >>>>>>> because I need to select Cube from a combo box below the query > > window > > >>>>>>> > > >>>>>>> Thank you very much > > >>>>>>> > > >>>>>>> On Wed, Nov 1, 2023 at 2:20 PM Xiaoxiang Yu <x...@apache.org> > > wrote: > > >>>>>>> > > >>>>>>>> From my side, I cannot understand why you say Kylin 4 is 'very > > >>>>>>>> obviously'. Can you give an example? > > >>>>>>>> From the source code, the basic logic of choosing the right > > >>>>>>>> cube/model are similar. > > >>>>>>>> ------------------------ > > >>>>>>>> With warm regard > > >>>>>>>> Xiaoxiang Yu > > >>>>>>>> > > >>>>>>>> > > >>>>>>>> > > >>>>>>>> On Wed, Nov 1, 2023 at 3:01 PM Nam Đỗ Duy <na...@vnpay.vn> > wrote: > > >>>>>>>> > > >>>>>>>>> Thank you for your kind reply, please answer 1 more question > > about > > >>>>>>>>> version 5: > > >>>>>>>>> > > >>>>>>>>> In version 4.x we run query against a Cube very obviously, but > in > > >>>>>>>>> version 5, the cube usage is a implication socan you advise: > for > > a given > > >>>>>>>>> query, which model will be used, which index (cube) will be > used > > for this > > >>>>>>>>> query? > > >>>>>>>>> > > >>>>>>>>> Thank you > > >>>>>>>>> > > >>>>>>>>> On Wed, Nov 1, 2023 at 1:42 PM Xiaoxiang Yu <x...@apache.org> > > >>>>>>>>> wrote: > > >>>>>>>>> > > >>>>>>>>>> 1. How do I measure the size of the index (cube) in version 5? > > >>>>>>>>>> You can check storage of specific Indexes from the Index > > page. > > >>>>>>>>>> > > >>>>>>>>>> > > > https://kylin.apache.org/5.0/docs/modeling/model_design/aggregation_group#view-aggregate-index > > >>>>>>>>>> or > > >>>>>>>>>> > > > https://kylin.apache.org/5.0/assets/images/index_1-6ad3f55183d4ed61962359d9408ba192.png > > >>>>>>>>>> > > >>>>>>>>>> > > >>>>>>>>>> 2. How to create the cardinality for each column? > > >>>>>>>>>> You should check this link : > > >>>>>>>>>> https://kylin.apache.org/5.0/docs/datasource/data_sampling/ . > > >>>>>>>>>> > > >>>>>>>>>> 3. In your default project sample named SSB project, you have > > >>>>>>>>>> only 4 simple aggregate group index and no table index as in > > attached file > > >>>>>>>>>> so what is the best strategy to select index for our OLAP? > > >>>>>>>>>> 1. There does exist a 'Base Table Index' by default > > >>>>>>>>>> actually, its id is 20000000001. > > >>>>>>>>>> 2. I think it is a good question and Kylin 5 lacks such a > > >>>>>>>>>> guide for better modeling. You are free to ask your question > to > > >>>>>>>>>> mailing list and I will try to reply. > > >>>>>>>>>> > > >>>>>>>>>> ------------------------ > > >>>>>>>>>> With warm regard > > >>>>>>>>>> Xiaoxiang Yu > > >>>>>>>>>> > > >>>>>>>>>> > > >>>>>>>>>> > > >>>>>>>>>> On Wed, Nov 1, 2023 at 2:12 PM Xiaoxiang Yu <x...@apache.org> > > >>>>>>>>>> wrote: > > >>>>>>>>>> > > >>>>>>>>>>> OK, I didn't read all the mail history so I misunderstand the > > >>>>>>>>>>> situation. Looks like you need to analyse > > >>>>>>>>>>> the cause why the query didn't hit the cube correctly. > > >>>>>>>>>>> > > >>>>>>>>>>> Please generate query diagnosis package and send it to me > > >>>>>>>>>>> privately. I will analyse the query log. > > >>>>>>>>>>> You can refer to the following steps in screenshots. > > >>>>>>>>>>> > > >>>>>>>>>>> [image: image.png] > > >>>>>>>>>>> > > >>>>>>>>>>> If the screenshots are not displaying correctly, please read > > >>>>>>>>>>> this guide : > > >>>>>>>>>>> > > >>>>>>>>>>> > > > https://kylin.apache.org/5.0/docs/operations/system-operation/diagnosis/#generate-query-diagnosis-package-in-web-ui > > >>>>>>>>>>> > > >>>>>>>>>>> By the way, you need to analyse the cause by reading > > >>>>>>>>>>> kylin.query.log, not the kylin.log, > > >>>>>>>>>>> refer to > > >>>>>>>>>>> https://kylin.apache.org/5.0/docs/operations/logs/system_log > > >>>>>>>>>>> > > >>>>>>>>>>> ------------------------ > > >>>>>>>>>>> With warm regard > > >>>>>>>>>>> Xiaoxiang Yu > > >>>>>>>>>>> > > >>>>>>>>>>> > > >>>>>>>>>>> > > >>>>>>>>>>> On Wed, Nov 1, 2023 at 12:18 PM Nam Đỗ Duy <na...@vnpay.vn> > > >>>>>>>>>>> wrote: > > >>>>>>>>>>> > > >>>>>>>>>>>> Thank you Xiaoxiang for your advice. As my title email > shown, > > I > > >>>>>>>>>>>> guessed that the OLAP functionalities has not been correctly > > set up in my > > >>>>>>>>>>>> computer. > > >>>>>>>>>>>> > > >>>>>>>>>>>> The evidence about it is that: when I disable the Pushdown > > >>>>>>>>>>>> option box to use solely the precomputation cube only, it > > showed following > > >>>>>>>>>>>> error: Please kindly advise how to properly build the OLAP > > >>>>>>>>>>>> > > >>>>>>>>>>>> LIMIT 500": No realization found for OLAPContext, > > MODEL_UNMATCHED_JOIN, > > rel#2240:KapTableScan.OLAP.[](table=[VNEVENT_HIVE_DWH_400MILLION_ROWS, > > FACTUSEREVENT],ctx=0@null,fields=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, > > 12, 13, 14, 15, 16, 17, 18, 19, 20]) > > >>>>>>>>>>>> > > >>>>>>>>>>>> > > >>>>>>>>>>>> > > >>>>>>>>>>>> On Wed, Nov 1, 2023 at 10:40 AM Xiaoxiang Yu < > x...@apache.org > > > > > >>>>>>>>>>>> wrote: > > >>>>>>>>>>>> > > >>>>>>>>>>>>> Hi, > > >>>>>>>>>>>>> > > >>>>>>>>>>>>> Yesterday, I tried to see if query pushdown functions > > work > > >>>>>>>>>>>>> well in the Kylin5 docker, and all of my queries return > > proper responses . > > >>>>>>>>>>>>> After checking your logs from Shaofeng, I found these > > >>>>>>>>>>>>> error messages repeated many times: > > >>>>>>>>>>>>> 1. 'java.io.IOException: All datanodes > > >>>>>>>>>>>>> DatanodeInfoWithStorage[127.0.0.1:9866 > > ,DS-5093899b-06c7-4386-95d5-6fc271d92b52,DISK] > > >>>>>>>>>>>>> are bad. Aborting...' > > >>>>>>>>>>>>> 2. 'curator.ConnectionState : Connection timed out for > > >>>>>>>>>>>>> connection string (localhost:2181) and timeout (15000) / > > elapsed (41794) > > >>>>>>>>>>>>> org.apache.curator.CuratorConnectionLossException: > > >>>>>>>>>>>>> KeeperErrorCode = ConnectionLoss' > > >>>>>>>>>>>>> > > >>>>>>>>>>>>> I guess the root cause is that the container didn't not > > >>>>>>>>>>>>> have enough resources. I found you query on a table called > > >>>>>>>>>>>>> 'XXX_hive_dwh_400million_rows', looks like you gave a > > complex query on a > > >>>>>>>>>>>>> table which contains 400 million rows? > > >>>>>>>>>>>>> > > >>>>>>>>>>>>> Since I am the uploader of kylin5 's docker image, I > want > > >>>>>>>>>>>>> to give some explainment. Kylin5 docker is not a place for > > performance > > >>>>>>>>>>>>> benchmarks, it is only for demonstration. It is only > > allocated with very > > >>>>>>>>>>>>> little resources(8G memory) if you are using the default > > command from > > >>>>>>>>>>>>> docker hub page. Before I uploaded my image, I only tested > > my image using > > >>>>>>>>>>>>> the ssb dataset, which the biggest table only contains > about > > 60k rows. If > > >>>>>>>>>>>>> you are using a larger dataset and complexer queries, you > > have to scale the > > >>>>>>>>>>>>> resource properly. Try querying tables which contain not > > more than 100k > > >>>>>>>>>>>>> rows by default. > > >>>>>>>>>>>>> > > >>>>>>>>>>>>> Here are some tips which may help you to check if the > > >>>>>>>>>>>>> daemon service is in health status and > > resources(particularly disk space) > > >>>>>>>>>>>>> is configured properly. > > >>>>>>>>>>>>> > > >>>>>>>>>>>>> 1. Checking HDFS 's web ui( > > >>>>>>>>>>>>> http://localhost:9870/dfshealth.html#tab-datanode ) to > > >>>>>>>>>>>>> confirm whether HDFS service is in 'In service' status. > > >>>>>>>>>>>>> 2. Checking Datanode 's log in > > >>>>>>>>>>>>> > > `/opt/hadoop-3.2.1/logs/hadoop-root-datanode-Kylin5-Machine.log`, check > if > > >>>>>>>>>>>>> there is any error message. Like: cat > > >>>>>>>>>>>>> > > /opt/hadoop-3.2.1/logs/hadoop-root-datanode-Kylin5-Machine.log | grep > ERROR > > >>>>>>>>>>>>> | wc -l > > >>>>>>>>>>>>> 3. Checking if your docker engine is configured with > > >>>>>>>>>>>>> enough disk space, if you are using Docker Desktop like > > me,please go to > > >>>>>>>>>>>>> "Settings" - "Resources" - "Advanced", make sure you have > > allocated 40GB+ > > >>>>>>>>>>>>> disk space to the docker container. > > >>>>>>>>>>>>> 4. Checking the available disk space of your container > by > > >>>>>>>>>>>>> `df -h`, make sure the 'Use%' of 'overlay' is less than > 60% . > > >>>>>>>>>>>>> 5. Checking the load average/ cpu usage/ jvm gc. Make > > sure > > >>>>>>>>>>>>> these metrics are not really high when you send a query. > > >>>>>>>>>>>>> ------------------------ > > >>>>>>>>>>>>> With warm regard > > >>>>>>>>>>>>> Xiaoxiang Yu > > >>>>>>>>>>>>> > > >>>>>>>>>>>>> > > >>>>>>>>>>>>> > > >>>>>>>>>>>>> On Tue, Oct 31, 2023 at 5:13 PM Nam Đỗ Duy > > >>>>>>>>>>>>> <na...@vnpay.vn.invalid> wrote: > > >>>>>>>>>>>>> > > >>>>>>>>>>>>>> Hi ShaoFeng > > >>>>>>>>>>>>>> > > >>>>>>>>>>>>>> Thank you very much for your valuable feedback > > >>>>>>>>>>>>>> > > >>>>>>>>>>>>>> I saw the application to be there (if I see it right) as > in > > >>>>>>>>>>>>>> the attachment photo. Kindly advise so that I can run this > > query on OLAP. > > >>>>>>>>>>>>>> > > >>>>>>>>>>>>>> PS. I sent you the log file in private. > > >>>>>>>>>>>>>> > > >>>>>>>>>>>>>> [image: image.png] > > >>>>>>>>>>>>>> > > >>>>>>>>>>>>>> On Tue, Oct 31, 2023 at 3:11 PM ShaoFeng Shi < > > >>>>>>>>>>>>>> shaofeng...@apache.org> wrote: > > >>>>>>>>>>>>>> > > >>>>>>>>>>>>>>> Can you provide the messages in logs/kylin.log when > > >>>>>>>>>>>>>>> executing the SQL? and you can also check the Spark UI > > from yarn resource > > >>>>>>>>>>>>>>> manager (there should be one running application called > > Spardar, which is > > >>>>>>>>>>>>>>> Kylin's backend spark application). If the application is > > not there, it may > > >>>>>>>>>>>>>>> indicates the yarn doesn't have resource to startup it. > > >>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>> Best regards, > > >>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>> Shaofeng Shi 史少锋 > > >>>>>>>>>>>>>>> Apache Kylin PMC, > > >>>>>>>>>>>>>>> Apache Incubator PMC, > > >>>>>>>>>>>>>>> Email: shaofeng...@apache.org > > >>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>> Apache Kylin FAQ: > > >>>>>>>>>>>>>>> https://kylin.apache.org/docs/gettingstarted/faq.html > > >>>>>>>>>>>>>>> Join Kylin user mail group: > > user-subscr...@kylin.apache.org > > >>>>>>>>>>>>>>> Join Kylin dev mail group: > dev-subscr...@kylin.apache.org > > >>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>> Nam Đỗ Duy <na...@vnpay.vn> 于2023年10月31日周二 10:35写道： > > >>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>>> Dear Sir/Madam, > > >>>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>>> I have a fact with 500million rows then I build model, > > >>>>>>>>>>>>>>>> index according to the website help. > > >>>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>>> I chose full incremental because this is the first > times I > > >>>>>>>>>>>>>>>> load data > > >>>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>>> I create both index types Aggregate group index, table > > >>>>>>>>>>>>>>>> index as photo attached. > > >>>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>>> But the query always failed after timeout of 300 seconds > > (I > > >>>>>>>>>>>>>>>> run in docker), I dont want to increase the value of 300 > > seconds because I > > >>>>>>>>>>>>>>>> wish the OLAP can run within 1 minutes (is that > possible?) > > >>>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>>> It seems that the OLAP function in indexing not working > to > > >>>>>>>>>>>>>>>> speedup the query by precomputed cube. > > >>>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>>> Can you advise to check whether the index did really > work? > > >>>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>>> It is quite urgent task for me so prompt response is > > highly > > >>>>>>>>>>>>>>>> appreciated. > > >>>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>>> Thank you very much > > >>>>>>>>>>>>>>>> > > >>>>>>>>>>>>>>> > > >
